DATABASE QUERIES ok so ive created a database for a website that handles student
ID: 3740943 • Letter: D
Question
DATABASE QUERIES
ok so ive created a database for a website that handles students swapping university classes
"user" being the student
"class" being the classes
"has" being the classes that the student currently has
"wants" being the classes that that the student doesnt have but wants
CREATE TABLE Class
(
ClassName varchar(255),
Professor_Name varchar(255),
Start_Time varchar(10),
End_Time varchar(10),
Course_Number varchar(20),
Section_Number varchar(20),
Days varchar(10),
PRIMARY KEY(Course_Number,Section_Number));
Now before creating table 'has' and 'wants' make sure user table is already created, as both these tables will take the reference from user table.
CREATE TABLE User
(
Email VARCHAR(255),
first_name VARCHAR(255),
last_name VARCHAR(255),
password VARCHAR(255),
PRIMARY KEY (Email)
);
CREATE TABLE Has
(
Email VARCHAR(255),
Course_Number VARCHAR(20),
Section_Number VARCHAR(20),
PRIMARY KEY (Email, Course_Number, Section_Number),
FOREIGN KEY (Email) REFERENCE Userr (Email),
FOREIGN KEY (Course_Number, Section_Number) REFERENCES Class (Course_Number, Section_Number)
);
CREATE TABLE Wants
(
Email VARCHAR(255),
Course_Number VARCHAR(20),
Section_Number VARCHAR(20),
PRIMARY KEY (Email, Course_Number, Section_Number),
FOREIGN KEY (Email) REFERENCE Userr (Email),
FOREIGN KEY (Course_Number, Section_Number) REFERENCES Class (Course_Number, Section_Number)
);
ive successfully created all the tables and added multiple classes to my class table, now im tryi ng to create users and add classes to the students "wants" and "has"
1. can someone help me write a query to show me how to create a user?
lets say i wanted to created a user for example
firstname: john
last name: smith
email: johnsmith@school.edu
password: star
2. once ive created that user how would i add a class to the users "has" and or "wants" table
lets say for example i wanted to add the following class to john smiths "has" table
ClassName Math
Professor_Name wendy brown
Start_Time 10:00 AM
End_Time 11:00 AM
Course_Number CDA 3103
Section_Number U01-C
Days Mo-We
can someone help me write a query to do this also please, im just looking for an example so i can see how to do it myself for the website.
please help
Professor Name Password Last Name Start Time Has Email End Time O,M) (O,M) Class User First Name (O,N) Wants Days Course_Number) Section NumberExplanation / Answer
Answer)
the existing query had some errors. Correct query is as below:
CREATE TABLE Class
(
ClassName varchar(255),
Professor_Name varchar(255),
Start_Time varchar(10),
End_Time varchar(10),
Course_Number varchar(20),
Section_Number varchar(20),
Days varchar(10),
PRIMARY KEY(Course_Number,Section_Number));
CREATE TABLE User
(
Email VARCHAR(255),
first_name VARCHAR(255),
last_name VARCHAR(255),
password1 VARCHAR(255),
PRIMARY KEY (Email)
);
CREATE TABLE Has
(
Email VARCHAR(255),
Course_Number VARCHAR(20),
Section_Number VARCHAR(20),
PRIMARY KEY (Email, Course_Number, Section_Number),
FOREIGN KEY (Email) REFERENCES User (Email),
FOREIGN KEY (Course_Number, Section_Number) REFERENCES Class (Course_Number, Section_Number)
);
CREATE TABLE Wants
(
Email VARCHAR(255),
Course_Number VARCHAR(20),
Section_Number VARCHAR(20),
PRIMARY KEY (Email, Course_Number, Section_Number),
FOREIGN KEY (Email) REFERENCES User (Email),
FOREIGN KEY (Course_Number, Section_Number) REFERENCES Class (Course_Number, Section_Number)
);
1.
lets say i wanted to created a user for example
firstname: john
last name: smith
email: johnsmith@school.edu
password: star
Answer)
INSERT INTO User
VALUES ('johnsmith@school.edu', 'john', 'smith', 'star');
select * from User;
Output:
Email first_name last_name password1
johnsmith@school.edu john smith star
2. once ive created that user how would i add a class to the users "has" and or "wants" table
lets say for example i wanted to add the following class to john smiths "has" table
ClassName Math
Professor_Name wendy brown
Start_Time 10:00 AM
End_Time 11:00 AM
Course_Number CDA 3103
Section_Number U01-C
Days Mo-We
Answer)
INSERT INTO Class
VALUES ('Math', 'wendy brown', '10:00 AM', '11:00 AM','CDA 3103', 'U01-C', 'Mo-We');
Class record is inserted in the above Class table.
INSERT INTO Has
VALUES ('johnsmith@school.edu', 'CDA 3103', 'U01-C');
This is the much information needed in the Has table.
select * from Has;
Output:
Email Course_Number Section_Number
johnsmith@school.edu CDA 3103 U01-C
INSERT INTO Wants
VALUES ('johnsmith@school.edu', 'CDA 3103', 'U01-C');
This is the much information needed in the Wants table.
select * from Wants;
Output:
Email Course_Number Section_Number
johnsmith@school.edu CDA 3103 U01-C
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.