Lab 2: Advanced SQL Queries and Multi-Table Operations

🎯 Learning Objectives

By the end of this lab, you will be able to: - Work with complex multi-table database schemas - Master JOIN operations (INNER, LEFT, RIGHT) - Use aggregate functions (COUNT, SUM, AVG, MAX, MIN) - Apply GROUP BY and HAVING clauses effectively - Write nested queries and subqueries - Understand set operations (UNION, INTERSECT) - Handle NULL values in queries

📋 Prerequisites

Completion of Lab 1 or equivalent knowledge
Understanding of basic SQL operations
Familiarity with primary and foreign keys

🏫 Scenario: University Database System

In this lab, we'll work with a university database containing information about students, courses, and enrollments. This realistic scenario will help you understand how databases are used in real-world applications.

Database Schema Overview

Our database consists of three related tables: - Student: Contains student information (ID, name, GPA) - Course: Contains course details (ID, code, name, credit hours) - Enrolled: Links students to courses with grades (many-to-many relationship)

🚀 Getting Started

Step 1: Create the Database

-- Clean slate: DROP existing DATABASE IF it EXISTS
DROP DATABASE IF EXISTS lab2;
CREATE DATABASE lab2;
USE lab2;

Step 2: Create the Table Structure

-- DROP TABLES IN correct ORDER (child TABLES first due to FOREIGN KEY constraints)
DROP TABLE IF EXISTS Enrolled;  -- Junction TABLE (child)
DROP TABLE IF EXISTS Student;   -- Parent TABLE
DROP TABLE IF EXISTS Course;    -- Parent TABLE

-- CREATE Student TABLE
CREATE TABLE Student (
    sid INT PRIMARY KEY,           -- Student ID (UNIQUE identifier)
    fname CHAR(20),               -- First name (fixed LENGTH)
    lname CHAR(20),               -- Last name (fixed LENGTH)
    gpa FLOAT                     -- Grade Point Average (can be NULL)
);

-- CREATE Course TABLE  
CREATE TABLE Course (
    cid INT PRIMARY KEY,          -- Course ID (UNIQUE identifier)
    code CHAR(10),               -- Course code (e.g., "CS101")
    name CHAR(40),               -- Course name (e.g., "Intro to Programming")
    credithours SMALLINT         -- Credit hours (1-4 typically)
);

-- CREATE Enrolled TABLE (junction TABLE FOR many-to-many relationship)
CREATE TABLE Enrolled (
    sid INT,                     -- Student ID (FOREIGN KEY)
    cid INT,                     -- Course ID (FOREIGN KEY)
    grade CHAR(2),               -- Letter grade (A+, A, B+, etc.)
    PRIMARY KEY (sid, cid),      -- Composite PRIMARY KEY
    FOREIGN KEY (sid) REFERENCES Student(sid),   -- Link to Student TABLE
    FOREIGN KEY (cid) REFERENCES Course(cid)     -- Link to Course TABLE
);

Key Design Concepts: - Composite Primary Key: (sid, cid) ensures one grade per student per course - Foreign Key Constraints: Maintain referential integrity - Many-to-Many Relationship: Students can enroll in multiple courses, courses can have multiple students

Step 3: Populate the Database with Sample Data

Insert Student Data

-- INSERT student records (SOME students have NULL GPA - NOT yet calculated)
INSERT INTO Student VALUES(1,'Amie','Massey',3.0);
INSERT INTO Student VALUES(2,'Abigail','Larsen',NULL);
INSERT INTO Student VALUES(3,'Cora','Rowland',4);
INSERT INTO Student VALUES(4,'Alesha','Ferrell',3.5);
INSERT INTO Student VALUES(5,'Nina','Lowery',NULL);
INSERT INTO Student VALUES(6,'Scarlet','Lane',2.6);
INSERT INTO Student VALUES(7,'Lukas','Reeves',3.6);
INSERT INTO Student VALUES(8,'Ray','Chang',3.2);
INSERT INTO Student VALUES(9,'Ioan','Reid',2.5);
INSERT INTO Student VALUES(10,'Ryan','Peck',3.1);
INSERT INTO Student VALUES(11,'Marco','Lowery',3.8);
INSERT INTO Student VALUES(12,'Pearl', 'Mayo',3.7);
INSERT INTO Student VALUES(13,'Elmer', 'Cain',2.7);
INSERT INTO Student VALUES(14,'Isla', 'Mccall',2.2);
INSERT INTO Student VALUES(15,'Kate', 'Kimmel',NULL);

Verify student data:

SELECT * FROM Student ORDER BY sid;

Insert Course Data

-- INSERT computer science courses WITH varying credit hours
INSERT INTO Course VALUES(1,'CS101', 'Computer Programming I',3);
INSERT INTO Course VALUES(2,'CS102', 'Computer Programming II',3);
INSERT INTO Course VALUES(3,'CS321', 'Data Structures',4);
INSERT INTO Course VALUES(4,'CS5710', 'Network',4);
INSERT INTO Course VALUES(5,'CS4550', 'DATABASE',4);
INSERT INTO Course VALUES(6,'CS5990', 'Capstone Project',4);
INSERT INTO Course VALUES(7,'CS6020', 'Data Mining',4);

Verify course data:

SELECT * FROM Course ORDER BY cid;

Insert Enrollment Data

-- CS101 enrollments (ALL 15 students enrolled)

INSERT INTO Enrolled VALUES(1,1, 'A+');
INSERT INTO Enrolled VALUES(2,1, 'A-');
INSERT INTO Enrolled VALUES(3,1, 'B');
INSERT INTO Enrolled VALUES(4,1, 'C');
INSERT INTO Enrolled VALUES(5,1, 'D');
INSERT INTO Enrolled VALUES(6,1, 'A');
INSERT INTO Enrolled VALUES(7,1, 'B-');
INSERT INTO Enrolled VALUES(8,1, 'C+');
INSERT INTO Enrolled VALUES(9,1, 'D');
INSERT INTO Enrolled VALUES(10,1, 'C+');
INSERT INTO Enrolled VALUES(11,1, 'A-');
INSERT INTO Enrolled VALUES(12,1, 'A');
INSERT INTO Enrolled VALUES(13,1, 'B+');
INSERT INTO Enrolled VALUES(14,1, 'C+');
INSERT INTO Enrolled VALUES(15,1, 'A');
INSERT INTO Enrolled VALUES(1,2, 'A');
INSERT INTO Enrolled VALUES(2,2, 'C');
INSERT INTO Enrolled VALUES(3,2, 'B+');
INSERT INTO Enrolled VALUES(4,2, 'C-');
INSERT INTO Enrolled VALUES(5,2, 'D+');
INSERT INTO Enrolled VALUES(6,2, 'A-');
INSERT INTO Enrolled VALUES(7,2, 'C-');
INSERT INTO Enrolled VALUES(8,2, 'C');
INSERT INTO Enrolled VALUES(9,2, 'D+');
INSERT INTO Enrolled VALUES(10,2, 'C+');
INSERT INTO Enrolled VALUES(11,2, 'A-');
INSERT INTO Enrolled VALUES(12,2, 'A');
INSERT INTO Enrolled VALUES(13,2, 'B-');
INSERT INTO Enrolled VALUES(14,2, 'C');

INSERT INTO Enrolled VALUES (1,3,'C-');
INSERT INTO Enrolled VALUES (2,3,'B-');
INSERT INTO Enrolled VALUES (3,3,'D+');
INSERT INTO Enrolled VALUES (4,3,'C');
INSERT INTO Enrolled VALUES (5,3,'A+');
INSERT INTO Enrolled VALUES (6,3,'D+');
INSERT INTO Enrolled VALUES (7,3,'F');
INSERT INTO Enrolled VALUES (8,3,'A-');
INSERT INTO Enrolled VALUES (9,3,'D+');
INSERT INTO Enrolled VALUES (10,3,'D');
INSERT INTO Enrolled VALUES (11,3,'A+');
INSERT INTO Enrolled VALUES (12,3,'A');
INSERT INTO Enrolled VALUES (13,3,'B-');
INSERT INTO Enrolled VALUES (14,3,'C');
INSERT INTO Enrolled VALUES (15,3,'B-');

INSERT INTO Enrolled VALUES (1,4,'F');
INSERT INTO Enrolled VALUES (2,4,'A');
INSERT INTO Enrolled VALUES (3,4,'D-');
INSERT INTO Enrolled VALUES (4,4,'A-');
INSERT INTO Enrolled VALUES (5,4,'B-');
INSERT INTO Enrolled VALUES (6,4,'A-');
INSERT INTO Enrolled VALUES (7,4,'D+');
INSERT INTO Enrolled VALUES (8,4,'B+');
INSERT INTO Enrolled VALUES (9,4,'C+');
INSERT INTO Enrolled VALUES (10,4,'B');
INSERT INTO Enrolled VALUES (11,4,'F');
INSERT INTO Enrolled VALUES (12,4,'F');
INSERT INTO Enrolled VALUES (13,4,'A+');
INSERT INTO Enrolled VALUES (14,4,'A+');

INSERT INTO Enrolled VALUES (1,5,'A+');
INSERT INTO Enrolled VALUES (2,5,'A+');
INSERT INTO Enrolled VALUES (3,5,'B');
INSERT INTO Enrolled VALUES (4,5,'D+');
INSERT INTO Enrolled VALUES (5,5,'C-');
INSERT INTO Enrolled VALUES (6,5,'B-');
INSERT INTO Enrolled VALUES (7,5,'B');
INSERT INTO Enrolled VALUES (8,5,'B+');
INSERT INTO Enrolled VALUES (9,5,'A');
INSERT INTO Enrolled VALUES (10,5,'B');
INSERT INTO Enrolled VALUES (11,5,'D');
INSERT INTO Enrolled VALUES (12,5,'B');
INSERT INTO Enrolled VALUES (13,5,'B');
INSERT INTO Enrolled VALUES (14,5,'A+');

INSERT INTO Enrolled VALUES (1,6,'A+');
INSERT INTO Enrolled VALUES (2,6,'D');
INSERT INTO Enrolled VALUES (3,6,'D-');
INSERT INTO Enrolled VALUES (4,6,'C-');
INSERT INTO Enrolled VALUES (5,6,'C');
INSERT INTO Enrolled VALUES (6,6,'C-');
INSERT INTO Enrolled VALUES (7,6,'A-');
INSERT INTO Enrolled VALUES (8,6,'D');
INSERT INTO Enrolled VALUES (9,6,'B');
INSERT INTO Enrolled VALUES (10,6,'B-');
INSERT INTO Enrolled VALUES (11,6,'F');
INSERT INTO Enrolled VALUES (12,6,'C+');
INSERT INTO Enrolled VALUES (15,6,'F');

Verify enrollment data:

-- CHECK total enrollments
SELECT COUNT(*) AS total_enrollments FROM Enrolled;

-- See sample enrollments WITH student AND course names
SELECT s.fname, s.lname, c.code, c.name, e.grade
FROM Student s
JOIN Enrolled e ON s.sid = e.sid
JOIN Course c ON e.cid = c.cid
LIMIT 10;

🔍 Query Exercises

Now that we have our database populated, let's practice various types of queries. Each exercise builds on previous concepts while introducing new techniques.

Exercise 1: Multi-Table JOIN with String Functions

Problem: Find all students who received an 'A' or 'A+' in the Database course, ordered by their full name.

Analysis: - We need data from all three tables: - Student table: for student names - Course table: to identify the Database course - Enrolled table: for grades - This requires joining three tables with two join conditions

Your Solution:

-- Write your query here

💡 Click to see the solution

SELECT CONCAT(fname, ' ', lname) AS student_name 
FROM Student s, Course c, Enrolled e
WHERE c.cid = e.cid 
  AND s.sid = e.sid
  AND (e.grade = 'A+' OR e.grade = 'A')
  AND c.name LIKE '%DATABASE%' 
ORDER BY student_name;

Alternative using explicit JOIN syntax:

SELECT CONCAT(s.fname, ' ', s.lname) AS student_name
FROM Student s
JOIN Enrolled e ON s.sid = e.sid
JOIN Course c ON e.cid = c.cid
WHERE (e.grade = 'A+' OR e.grade = 'A')
  AND c.name LIKE '%DATABASE%'
ORDER BY student_name;

Expected Output:

student_name
Abigail Larsen
Amie Massey
Isla Mccall

Key Concepts: - `CONCAT()`: Combines first and last names - Multiple table joins: Connect related data across tables - `LIKE '%Database%'`: Pattern matching for course names - `OR` condition: Multiple grade criteria

Query 2

Find all countries that manufacture some product in the ‘Gadgets’ category. Assume you have the following data:

DROP TABLE IF EXISTS Company;
DROP TABLE IF EXISTS Product;

CREATE TABLE Product(
    PName CHAR(20) PRIMARY KEY,
    Price DOUBLE,
    category CHAR(20),
    Manufacturer CHAR(20)
);

CREATE TABLE Company(
    CName CHAR(20) PRIMARY KEY,
    StockPrice DOUBLE,
    Country CHAR(20)
);

ALTER TABLE Product ADD FOREIGN KEY (Manufacturer) REFERENCES Company(CName);

INSERT INTO Company VALUES('GizmoWorks',25,'USA');
INSERT INTO Company VALUES('Canon',65,'Japan');
INSERT INTO Company VALUES('Hitachi',15, 'Japan');

INSERT INTO Product VALUES('Gizmo',19.99,'Gadgets','GizmoWorks');
INSERT INTO Product VALUES('Powergizmo',29.99,'Gadgets','GizmoWorks');
INSERT INTO Product VALUES('SingleTouch',149.99,'Photography','Canon');
INSERT INTO Product VALUES('MultiTouch',203.99,'Household','Hitachi');
SELECT Country FROM Product, Company
WHERE Manufacturer=CName AND Category='Gadgets'

This would return: Country USA USA How to get USA only once.

Query 3: An Unintuitive Query

Prepare the database as follows:

DROP TABLE IF EXISTS R;
DROP TABLE IF EXISTS T;
DROP TABLE IF EXISTS S;

CREATE TABLE R(A INT);
CREATE TABLE S(A INT);
CREATE TABLE T(A INT);

INSERT INTO R VALUES (1);
INSERT INTO R VALUES (1);
INSERT INTO R VALUES (2);
INSERT INTO R VALUES (3);
INSERT INTO R VALUES (5);
INSERT INTO R VALUES (7);
INSERT INTO R VALUES (7);
INSERT INTO R VALUES (8);

INSERT INTO S VALUES (1);
INSERT INTO S VALUES (1);
INSERT INTO S VALUES (2);
INSERT INTO S VALUES (3);

INSERT INTO T VALUES (1);
INSERT INTO T VALUES (2);
INSERT INTO T VALUES (3);
INSERT INTO T VALUES (7);

Run this query

SELECT  DISTINCT R.A
FROM R, S, T
WHERE R.A=S.A OR R.A=T.A

This would return the following

A
1
2
3
7

Remove all tuples from S

SET SQL_SAFE_UPDATES = 0;
DELETE FROM S;
SET SQL_SAFE_UPDATES = 1;

MySQL has something called safe mode, to disable it. We run SET SQL_SAFE_UPDATES = 0; and to enable it again SET SQL_SAFE_UPDATES = 1; Rerun the query

SELECT DISTINCT R.A
FROM   R, S, T
WHERE  R.A=S.A OR R.A=T.A

Multiset Operations

MySQL does not have INTERSECT or EXCEPT keyword. In the next lecture, we will explain an alternative. Compare these two queries:

SELECT  R.A 
FROM   R, S 
WHERE  R.A=S.A
UNION  
SELECT R.A 
FROM   R, T  
WHERE  R.A=T.A

SELECT  R.A
FROM   R, S
WHERE  R.A=S.A
UNION ALL
SELECT R.A
FROM   R, T
WHERE  R.A=T.A

Try the following SQL code

DROP TABLE IF EXISTS Product;
DROP TABLE IF EXISTS Company;

CREATE TABLE Product(
  PName CHAR(20) PRIMARY KEY,
    maker CHAR(20),
    factory_loc CHAR(20)
);
CREATE TABLE Company(
  CName CHAR(20) PRIMARY KEY,
    hq_city CHAR(20)
);

ALTER TABLE Product ADD FOREIGN KEY (maker) REFERENCES Company(CName);

INSERT INTO Company VALUES('X co.','Seattle');
INSERT INTO Company VALUES('Y inc.','Seattle');
INSERT INTO Company VALUES('A co.', 'Tokyo');

INSERT INTO Product VALUES('X','X co.','US');
INSERT INTO Product VALUES('A','A co.','China');
INSERT INTO Product VALUES('A+','A co.','US');
INSERT INTO Product VALUES('Y','Y inc.','China');

Compare the three queries below:

SELECT hq_city
FROM   Company, Product
WHERE  maker = cname 
  AND factory_loc='US'
AND hq_city IN (
SELECT hq_city
FROM   Company, Product
WHERE  maker = cname
AND factory_loc='China'
);

SELECT maker, hq_city
FROM   Company, Product
WHERE  maker = cname 
  AND factory_loc='US'
AND (maker,hq_city) IN (
SELECT maker, hq_city
FROM   Company, Product
WHERE  maker = cname
AND factory_loc='China'
);

SELECT DISTINCT hq_city
FROM   Company, Product
WHERE  maker = cname 
       AND cname IN (
        SELECT maker
        FROM   Product
        WHERE  factory_loc = 'US')
     AND cname IN (
        SELECT maker
        FROM   Product
        WHERE  factory_loc = 'China');

To Delete or update multiple rows

To disable safe updates or delete

SET SQL_SAFE_UPDATES=0;

To enable them back

SET SQL_SAFE_UPDATES=0;

Lab2.2

Run the following query

SELECT DISTINCT hq_city
FROM   Company, Product
WHERE  maker = cname 
       AND cname IN (
    SELECT maker
      FROM   Product
      WHERE  factory_loc = 'US')
   AND cname IN (
    SELECT maker
      FROM   Product
      WHERE  factory_loc = 'China')

To understand the previous query, you may break it into three parts.

Q1:

    SELECT maker
    FROM   Product
      WHERE  factory_loc = 'US'

Q2:


SELECT maker
      FROM   Product
      WHERE  factory_loc = 'China'

Q3:

SELECT *
FROM   Company, Product
WHERE  maker = cname

Another way:

SELECT DISTINCT hq_city
FROM   Company, (SELECT maker
      FROM   Product
      WHERE  factory_loc = 'US') US, 
      (SELECT maker
      FROM   Product
      WHERE  factory_loc = 'China') Ch
WHERE  us.maker = cname 
AND    ch.maker= cname

Another one:

SELECT DISTINCT hq_city
FROM   Company, Product p1,   Product p2
WHERE  p1.maker = cname 
AND    p2.maker= cname 
AND  p1.factory_loc = 'US'
AND  p2.factory_loc = 'China';

Example

DROP TABLE IF EXISTS company;
DROP TABLE IF EXISTS Product;
DROP TABLE IF EXISTS Purchase;

CREATE TABLE Company (
cname CHAR(20) PRIMARY KEY,
city CHAR(20)
);

CREATE TABLE Product (
  pname CHAR(20) ,
  maker CHAR(20),
  price DOUBLE,
  FOREIGN KEY (maker) REFERENCES company(cname),
  PRIMARY KEY (pname, maker)
);

CREATE TABLE Purchase 
(id INT PRIMARY KEY,
 product CHAR(20),
 buyer VARCHAR(30),
FOREIGN KEY (product) REFERENCES Product(pname)
 );

INSERT INTO Company VALUES('X co','Seattle');
INSERT INTO Company VALUES('Y co','Jeffersonville');
INSERT INTO Company VALUES('A co','Seattle');
INSERT INTO Company VALUES('A inc','LA');
INSERT INTO Company VALUES('B co','NYC');
INSERT INTO Product VALUES('X','X co',20);
INSERT INTO Product VALUES('X+','X co',10);
INSERT INTO Product VALUES('Y','Y co',5);
INSERT INTO Product VALUES('A','A co',8);
INSERT INTO Product VALUES('T','A co',18);
INSERT INTO Product VALUES('A','A inc',5);
INSERT INTO Product VALUES('AA','A co',30);
INSERT INTO Product VALUES('AAA','A co',20);
INSERT INTO Product VALUES('B','B co',8);
INSERT INTO Purchase VALUES(1,'A','Joe Blow');
INSERT INTO Purchase VALUES(2,'A','Joe Smith');
INSERT INTO Purchase VALUES(3,'A','John Oliver');
INSERT INTO Purchase VALUES(4,'A','John Oliver');
INSERT INTO Purchase VALUES(5,'Y','Joe Blow');
INSERT INTO Purchase VALUES(6,'X','Joe Blow');
INSERT INTO Purchase VALUES(7,'A','Joe Blow');
INSERT INTO Purchase VALUES(8,'A','Joe Blow');
INSERT INTO Purchase VALUES(9,'A','Joe Blow');

Query Q1

Cities where one can find companies that manufacture products bought by "Joe Blow”.

SELECT  c.city
FROM   Company c
WHERE  c.cname  IN (
  SELECT pr.maker
      FROM   Purchase p, Product pr
      WHERE  p.product = pr.pname 
     AND p.buyer = 'Joe Blow')

Note that we got Seattle twice for X co and A co

Query Q2

SELECT c.city
 FROM   Company c, 
        Product p, 
        Purchase pu
 WHERE  c.cname = p.maker
   AND  p.pname = pu.product
   AND  pu.buyer = 'Joe Blow'

Compare the previous two queries.

Question 1

Find products that are more expensive than all those produced by “X co”

Solution

SELECT pname
FROM   Product
WHERE  price > ALL(
  SELECT price
     FROM   Product
     WHERE  maker = 'X co')

Question 2

Find ‘copycat’ products, i.e. products made by competitors with the same names as products made by ‘A co’

Solution

 SELECT p1.pname
FROM   Product p1
WHERE  p1.maker = 'A co'
   AND EXISTS(
  SELECT p2.pname
      FROM   Product p2
      WHERE  p2.maker <> 'A co'
     AND p1.pname = p2.pname)

DROP TABLE Purchase;
CREATE TABLE Purchase (
product CHAR(20), 
pdate DATE, 
price DOUBLE,
 quantity INT
);
INSERT INTO Purchase VALUES('bagel','2021-10-21',1,20);
INSERT INTO Purchase VALUES('banana','2021-10-3',0.5,10);
INSERT INTO Purchase VALUES('banana','2021-10-10',1,10);
INSERT INTO Purchase VALUES('bagel','2021-10-25',1.5,20);

Note that we do not have a primary key.

Get the number of purchases.

sql SELECT COUNT(*) FROM purchase

Find the sales after ‘10/10/2021’

  SELECT product,
SUM(price * quantity) AS TotalSales
FROM Purchase
WHERE pdate > '2021-10-10'
GROUP BY product

Another way for the same query:


SELECT DISTINCT  x.product,
(SELECT SUM(y.price*y.quantity)  
FROM Purchase y WHERE  x.product = y.product AND y.pdate > '2021-10-10')   TotalSales FROM Purchase x WHERE  x.pdate > '2021-10-10'

HAVING Clause

Same query as before, except that we consider only products that have more than 100 buyers sql SELECT product, SUM(price*quantity) FROM Purchase WHERE pdate > '2021-10-1' GROUP BY product HAVING SUM(quantity) > 100

Purchase

Id	Product	Price	Quantity
1	bagel	1.0	5
2	banana	0.5	6
3	Juice	1.0	4
4	donuts	1.5	8
5	bagel	1.5	7
6	banana	1.0	4
7	banana	1.0	3
8	juice	3.0	5


DROP TABLE IF EXISTS Purchase;
CREATE TABLE Purchase (
  ID INT PRIMARY KEY AUTO_INCREMENT,
  Product CHAR(20),
  Price DECIMAL(10,2),
  Quantity INT
  );

INSERT INTO Purchase(Product, Price, Quantity) VALUES('bagel',1.0,5),
('banana',.5,6), ('juice',1.0,4),
('donuts', 1.5,8), ('bagel', 1.5,7),
('banana',1.0,4),('banana',1,3), ('juice',3.0,5)
;

NOTE: AUTO_INCREMENT, and how we insert into the table.

SELECT SUM(Price*Quantity) AS Total_sales
FROM Purchase
WHERE product='bagel'

Executing from & WHERE

Id	Product	Price	Quantity
1	bagel	1.0	5
5	bagel	1.5	7

Computing the aggregate.

Price	X	Quantity
1.0	x	5
1.5	x	7
15.5

COUNT() vs COUNT(DISTINCT)

Run the following two queries:

SELECT COUNT(*) 
FROM Purchase;

SELECT COUNT(DISTINCT product)
FROM Purchase;

First query returns 8 while the second returns 4.

Group By clause

The following query finds the total number of units sold across all products.

SELECT SUM(Quantity) 
FROM Purchase

The query returns 36. To find the number of units sold for each product.

SELECT Product, SUM(Quantity) 
FROM Purchase
GROUP BY Product

Find products and sales that have sold more than 10 units.

Solution

SELECT Product, SUM(Quantity*Price)
FROM Purchase
GROUP BY Product
HAVING SUM(Quantity)> 10

Product	SUM(Quantity*Price)
bagel	15.50
banana	10.00

Find the product with the most units sold.

Solution


  SELECT Product, SUM(Quantity*Price)
FROM Purchase
GROUP BY Product
HAVING SUM(Quantity)= (
  SELECT MAX(t.units) 
  FROM  
  (SELECT SUM(Quantity) AS units 
   FROM Purchase
   GROUP BY Product) t
);

To understand the query, let us start with:

SELECT SUM(Quantity) 
FROM Purchase
GROUP BY Product

SELECT MAX(t.units) 
FROM  (SELECT SUM(Quantity) AS units 
     FROM Purchase
     GROUP BY Product) t

The query above finds the maximum of the total unit sold.

Run the following queries, to create the schema:

DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS supplier;

CREATE TABLE supplier(
id INT,
sname VARCHAR(30) NOT NULL,
PRIMARY KEY (id),
UNIQUE(sname)
);

CREATE TABLE product(
id INT PRIMARY KEY, 
pname VARCHAR(30) NOT NULL,
price FLOAT,
supplierid INT,
FOREIGN KEY (supplierid) REFERENCES supplier(id)
);

Insert Data

INSERT INTO supplier(id, sname) VALUES (1, 'bike Ltd');
INSERT INTO supplier(id, sname) VALUES (2, 'bike USA');
INSERT INTO supplier(id, sname) VALUES (3, 'bikes4all');

INSERT INTO product VALUES (1,'girl bike', 110, 1);
INSERT INTO product VALUES (2,'boy bike', 110, 2);
INSERT INTO product VALUES (3,'mountain bike', 510, 2);
INSERT INTO product VALUES (4,'bike', 180, NULL );
INSERT INTO product VALUES (5,'ebike', 280, NULL );

Try to insert the following

INSERT INTO supplier(id, sname) VALUES (4, 'bike Ltd');

The previous SQL insert should not work, as we have specified that the supplier name to be unique.

Q1:

Find all products with unknown supplier.

A supplier of a product is recorded in product table to find out products with unknown supplier, we need to find those that are NULL.

Solution

  SELECT * FROM product WHERE supplierid IS NULL

Q2: Find all suppliers with no product listed.

Solution


SELECT * FROM Supplier s
WHERE NOT EXISTS (SELECT * FROM product p
  WHERE p.supplierid=s.id)

Q3: For each product list the supplier information. <details> <summary>Solution</summary> ```sql SELECT * FROM supplier s, Product p WHERE s.id = p.supplierid ``` Another way to write the above query is by using join clause, as follow: ```sql select * from product p join supplier s on p.supplierid=s.id; However, running the above query, you are not returning information about Products with no supplier information (e.g., products with id 4 and 5).

  SELECT * FROM product p 
LEFT OUTER JOIN supplier s 
ON p.supplierid=s.id;

Q4: Find supplier (along with the supplied products)

Solution

SELECT * FROM product p
RIGHT OUTER JOIN supplier s 
ON p.supplierid=s.id;

Q5 Find supplier and products including not matched

Solution

SELECT * FROM product p 
full OUTER JOIN supplier s 
ON p.supplierid=s.id;

Mysql does not support full outer join. It is can executed as follow:

SELECT * FROM product p 
LEFT OUTER JOIN supplier s 
ON p.supplierid=s.id
UNION
SELECT * FROM product p 
RIGHT OUTER JOIN supplier s 
ON p.supplierid=s.id;

Division


 CREATE TABLE student (SID INT PRIMARY KEY,Name VARCHAR(10),Major
 VARCHAR(4)); 
 CREATE TABLE enrolled (SID INT,cid VARCHAR(5),PRIMARY KEY
 (sid,cid)); 
 INSERT INTO student VALUES (40,'alex','cs'), (41, 'john','cs'), (42, 'mat', 'cs'), (43, 'zack', 'math');
 INSERT INTO Enrolled VALUES (40,'cs101'),(40,  'cs102'),(41, 'cs101'),(41,  'cs404'),(41,'cs405'), (42,'cs101'),(42,'cs102'),(42,'cs203'),(43,'cs101'),(43,'cs102');

Find students who are taking all courses taken by student 40

Solution


SELECT DISTINCT NAME FROM STUDENT S WHERE major='cs’ AND NOT EXISTS
 (SELECT * FROM ENROLLED E2 WHERE SID=40 AND CID NOT IN (SELECT CID
 FROM ENROLLED E WHERE E.SID=S.SID) )

  SELECT DISTINCT NAME FROM STUDENT S WHERE major=‘cs’ AND  NOT EXISTS
     (SELECT * FROM ENROLLED E2  WHERE SID=40 AND NOT EXISTS ( SELECT *
     FROM ENROLLED E WHERE E.SID=S.SID AND E2.CID=E.CID) )