This Project asks you to submit a SQL text file (e.g. project2.sql) with all your answers to all the questions listed in this assignment. Your answers will be written in SQL format. All SQL statements will be tested in a MySQL database including: CREATE TABLE, ALTER TABLE, INSERT and SELECT. You must use the following table structures for your MySQL DDL and DML commands:
STUDENT(StudentID, LastName, FirstName, Address, City, State, Zip, Phone)
StudentID will be automatically increased integer identifier
Choose VARCHAR or CHAR based on your personal understanding of the fields for the rest part of the relation/table
STUDENT_TEXTBOOK(StudentID, TextbookID, Class, Semester, Year)
Semester would be CHAR
Year would be integer
TEXTBOOK(TextbookID, Description, Price, Location, BookstoreID)
TextbookID will be automatically increased integer identifier
Price is a decimal with 2 decimal places after zero
Description and Location can be VARCHAR or CHAR based on your personal understanding of the fields
BOOKSTORE(BookstoreID, Name, Address, City, State, Phone)
BookstoreID will be automatically increased integer identifier
Choose VARCHAR or CHAR based on your personal understanding of the fields for the rest part of the relation/table
SALES(SalesID, StudentID, TextbookID, NumberofPurchase, SalesDate)
NumberofPurchase is number of copies purchased
NOTE:
- You must use MySQL.
- Create tables with columns, data types and populate your own data
- Underscore represents a part of a primary key
- Underscore and italic represent both part of the primary key and foreign key.
- Italic represent foreign key
II. PLACE ANSWERS in a text file (e.g. project2.sql) NOT A WORD DOC
A. List the 5 DDL “CREATE TABLE” statements including the primary keys. (10 points)
B. List any DDL statements that add the foreign key(s) constraints using “ALTER TABLE”.
C. List at least 3 DDL INSERT statements (per table) to populate data in all 5 tables.
D. Write a separate SQL query for each of the 10 questions below.
1. List all Student LAST and FIRST NAMES who are from a city starting with a selected word with at least two letters long.
2. List all Student IDs who have textbook sales totals greater than a selected value.
3. List the last name, first name, and phone of the Students who made a purchase with three selected SalesIDs. Use a subquery.
4. Answer the previous question but use an inner join.
5. List all student names and phone numbers. If they had purchased textbooks in the year 2012, then list the textbook descriptions. Otherwise, allow the textbook description to be null. You can use the default data format DD-MON-YY. (Hint: you will use a left outer join.)
6. List last name, first name, and phone of students who have purchased a textbook that has a price greater than a given number/value. (Use subquery).
7. Rewrite the last questions using an inner join and alias for table names.
8. List last name, first name, and phone of Students who have purchased a Textbook that was supplied by a bookstore with a name that begins with a given English letter. (Use subquery).
9. Rewrite the previous question using an inner join of the tables and alias for table names.
10. List the total “sum” of textbook prices for each student in descending order by last name and first name, class, textbook description AND total purchases for a given year. Use an inner join for the tables. NOTE: The business rule for the STUDENT_TEXTBOOK table assumes that a student will only purchase one textbook at a time.
Solution:
--A)Creating talble
CREATE TABLE STUDENT(
StudentID INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
LastName VARCHAR(30) NOT NULL,
FirstName VARCHAR(30) NOT NULL,
Address VARCHAR(50) NOT NULL,
City VARCHAR(50) NOT NULL,
State VARCHAR(30) NOT NULL,
Zip INT(10) NOT NULL,
Phone INT NOT NULL
);
CREATE TABLE BOOKSTORE(
BookstoreID INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(30) NOT NULL,
Address VARCHAR(30) NOT NULL,
City VARCHAR(10) NOT NULL,
State VARCHAR(30) NOT NULL,
Phone INT NOT NULL,
PRIMARY KEY(BookstoreID)
);
CREATE TABLE TEXTBOOK(
TextbookID INT NOT NULL AUTO_INCREMENT,
Description TEXT NOT NULL,
Price INT NOT NULL,
Location VARCHAR(50) NOT NULL,
BookstoreID INT NOT NULL,
PRIMARY KEY(TextbookID)
);
CREATE TABLE STUDENT_TEXTBOOK(
StudentID INT NOT NULL,
TextbookID INT NOT NULL,
Class INT NOT NULL,
Semester CHAR NOT NULL,
Year INT(4) NOT NULL,
PRIMARY KEY(StudentID, TextbookID)
);
CREATE TABLE SALES(
SalesID INT NOT NULL AUTO_INCREMENT,
StudentID INT NOT NULL,
TextbookID INT NOT NULL,
NumberofPurchase INT NOT NULL,
SalesDate DATE NOT NULL,
PRIMARY KEY(SalesID)
);
--B:
ALTER TABLE TEXTBOOK
ADD FOREIGN KEY (BookstoreID) REFERENCES BOOKSTORE(BookstoreID) ON DELETE CASCADE;
ALTER TABLE STUDENT_TEXTBOOK
ADD FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID) ON DELETE CASCADE,
ADD FOREIGN KEY (TextbookID) REFERENCES TEXTBOOK(TextbookID) ON DELETE CASCADE;
ALTER TABLE SALES
ADD FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID) ON DELETE CASCADE,
ADD FOREIGN KEY (TextbookID) REFERENCES TEXTBOOK(TextbookID) ON DELETE CASCADE;
--C:
INSERT INTO STUDENT(LastName, FirstName, Address, City, State, Zip, Phone)
VALUES ( 'Western', 'John', 'NEW COLONY', 'Bangalore', 'MAHARASTRA', '234523', '1658799634'),
('PRAKAR', 'AKASH', 'OLD COLONY', 'Mumbai', 'KARNATAKA', '234523', '123456356'),
( 'Roy', 'Aditya', 'Round street', 'Mumbai', 'Karnataka', '234321', '123456789');
INSERT INTO BOOKSTORE( Name, Address, City, State, Phone)
VALUES ( 'Lord of the ring', 'Lord colony', 'Bangalore', 'Karnataka', '43165789'),
( 'Starswars', 'Starswars colony', 'Mumbai', 'Maharastra', '123765893'),
( 'Hunger Games', 'Hunger colony', 'Delhi', 'Maharastra', '123456');
INSERT INTO TEXTBOOK( Description, Price, Location, BookstoreID)
VALUES ('About space adventure and spock.', '250', 'Bangalore','1'),
( 'About survival and learning skills.', '280', 'Mumbai','2'),
( 'About Kings and my presious.', '450', 'Chennai','3'),
( 'About kingdoms PartII.', '260', 'bangalore','1');
INSERT INTO STUDENT_TEXTBOOK(StudentID, TextbookID, Class, Semester, Year)
VALUES ('2', '1', '6', '9', '2012'),
('3', '2', '7', '7', '2017'),
('2', '3', '8', '4', '2015'),
('1', '4', '8', '4', '2012');
INSERT INTO SALES( StudentID, TextbookID, NumberofPurchase, SalesDate)
VALUES ( '2', '4', '1', '2012-12-23'),
( '3', '2', '1', '2015-06-21'),
( '1', '1', '1', '2012-07-14'),
( '3', '2', '1', '2016-04-23'),
( '1', '3', '1', '2017-02-16'),
( '3', '1', '1', '2017-08-03');
--D:
--Query 1:
SELECT LastName,FirstName FROM STUDENT
WHERE City LIKE 'M%' AND LENGTH(City)>=2 ;--Query 2:
SELECT StudentID FROM SALES
WHERE NumberofPurchase >2;--Query 3:
SELECT LastName,FirstName,Phone FROM STUDENT
WHERE StudentID IN ( SELECT SalesID FROM SALES
WHERE SalesID=1 OR
SalesID=2 OR
SalesID=3
);--Query 4:
SELECT LastName, FirstName, Phone
FROM STUDENT t1
INNER JOIN SALES t2
ON t1.StudentID=t2.StudentID
WHERE t2.SalesID =1 OR
t2.SalesID=2 OR
t2.SalesID=5;--Query 5:
SELECT x1.FirstName,x1.LastName,x1.Phone,EXTRACT(YEAR FROM x2.SalesDate) AS Year,
CASE
WHEN EXTRACT(YEAR FROM x2.SalesDate)!=2012 THEN NULL
ELSE x3.Description
END AS Description
FROM STUDENT AS x1
LEFT JOIN SALES As x2
ON (x1.StudentID=x2.StudentID)
LEFT JOIN TEXTBOOK as x3
ON( x2.TextbookID=x3.TextbookID)--Query 6:
SELECT LastName, FirstName, Phone
FROM STUDENT
WHERE StudentID IN (SELECT StudentID
FROM STUDENT_TEXTBOOK
WHERE TextbookID IN (SELECT TextbookID
FROM TEXTBOOK
WHERE Price>270
));--Query 7:
SELECT LastName, FirstName, Phone
FROM STUDENT AS x1
INNER JOIN STUDENT_TEXTBOOK AS x2
ON (x1.StudentID=x2.StudentID)
INNER JOIN TEXTBOOK AS x3
ON (x2.TextbookID=x3.TextbookID)
WHERE x3.Price>270;--Query 8:
SELECT LastName, FirstName, Phone
FROM STUDENT
WHERE StudentID IN (SELECT StudentID
FROM STUDENT_TEXTBOOK
WHERE TextbookID IN (SELECT TextbookID
FROM TEXTBOOK
WHERE BookstoreID IN ( SELECT BookstoreID
FROM BOOKSTORE
WHERE Name LIKE 'L%')));--Query 9:
SELECT LastName, FirstName, x1.Phone
FROM STUDENT AS x1
INNER JOIN STUDENT_TEXTBOOK AS x2
ON (x1.StudentID=x2.StudentID)
INNER JOIN TEXTBOOK AS x3
ON (x2.TextbookID=x3.TextbookID)
INNER JOIN BOOKSTORE AS x4
ON (x3.BookstoreID=x4.BookstoreID)
WHERE x4.Name LIKE 'L%';--Query 10:
SELECT EXTRACT(YEAR FROM x4.SalesDate) AS Year,x1.FirstName,x1.LastName,x2.Class,x3.Description,SUM(x4.NumberofPurchase) AS total_purchases,SUM(x3.Price) AS sum
FROM STUDENT AS x1
INNER JOIN STUDENT_TEXTBOOK AS x2
ON (x1.StudentID=x2.StudentID)
INNER JOIN TEXTBOOK AS x3
ON (x2.TextbookID=x3.TextbookID)
INNER JOIN SALES AS x4
ON ( x3.TextbookID=x4.TextbookID)
GROUP BY EXTRACT(YEAR FROM x4.SalesDate)
ORDER BY x1.LastName DESC;Please Write your comments and send another solution after try itself and if you need any other MySQL project help with an affordable price then we are ready to help you.
Send your database project requirement at realcode4you@gmail.com and get instant help from our database expert.