MySQL Practice Set | MySQL Database Design And Implementation



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.

36 views0 comments