Multi Table Queries

CREATE TABLE company (
    cname VARCHAR(20) PRIMARY KEY, -- company name uniquely identifies the company.
    stockprice DECIMAL(10,2), -- stock price IS IN money 
    country VARCHAR(10)); -- country IS just a string
INSERT INTO company VALUES ('GizmoWorks', 25.0, 'USA');
INSERT INTO company VALUES ('Canon', 65.0, 'Japan');
INSERT INTO company VALUES ('Hitachi', 15.0, 'Japan');

Another Example

DROP TABLE IF EXISTS product; 
DROP TABLE IF EXISTS company;

CREATE TABLE company (
    cname VARCHAR(20) PRIMARY KEY, -- company name uniquely identifies the company.
    stockprice DECIMAL(10,2), -- stock price IS IN money 
    country VARCHAR(10)); -- country IS just a string
INSERT INTO company VALUES ('ToyWorks', 25.0, 'USA');
INSERT INTO company VALUES ('ToyFriends', 65.0, 'China');
INSERT INTO company VALUES ('ToyCo', 15.0, 'China');

CREATE TABLE product(
       pname VARCHAR(10), -- name of the product
       price DECIMAL(10,2), -- price of the product
       category VARCHAR(10), -- category
       manufacturer VARCHAR(10), -- manufacturer
       PRIMARY KEY (pname, manufacturer),
       FOREIGN KEY (manufacturer) REFERENCES company(cname));
INSERT INTO product VALUES('Pikachu', 19.99, 'Toy', 'ToyWorks');
INSERT INTO product VALUES('Pikachu', 19.99, 'Toy', 'ToyFriends');
INSERT INTO product VALUES('Pokeball', 29.99, 'Electronic', 'ToyCo');
INSERT INTO product VALUES('Bulbasaur', 149.99, 'Toy', 'ToyFriends');
INSERT INTO product VALUES('Charizard', 203.99, 'Toy', 'ToyCo');
INSERT INTO product VALUES('PokeCamera', 19.99, 'Electronic', 'ToyWorks');

Our goal is to answer the following question: Find all categories of products that are made by Chinese companies

In Class Exerices

DROP TABLE IF EXISTS Movies;
CREATE TABLE Movies(title VARCHAR(50), YEAR INT, director VARCHAR(50), LENGTH INT);
INSERT INTO Movies VALUES('DATABASE Wars', 1967, 'John Joe', 123);
INSERT INTO Movies VALUES('The Databaser', 1992, 'John Bob', 190);
INSERT INTO Movies VALUES('DATABASE Wars', 1998, 'John Jim', 176);

Exercise

Can you write the movie query from lecture as a single SFW query?

Recall that we are trying to find all movie titles that were used for more than one movie, and our schema for the movies table is:

title VARCHAR(50)
YEAR INT
director VARCHAR(50)
LENGTH INT

Let's try to write the nested query that solves this from lecture:

use ANY to write it.

use join to write it.

Another example

DROP TABLE IF EXISTS Movies;
CREATE TABLE Movies(title VARCHAR(50), YEAR INT, director VARCHAR(50), LENGTH INT);
INSERT INTO Movies VALUES('DATABASE Wars', 1967, 'John Joe', 123);
INSERT INTO Movies VALUES('The Databaser', 1992, 'John Bob', 190);
INSERT INTO Movies VALUES('DATABASE Wars', 1998, 'John Jim', 176);

Exercise #1

Can you write the movie query from lecture as a single SFW query?

Recall that we are trying to find all movie titles that were used for more than one movie, and our schema for the movies table is:

title STRING year INT director STRING length INT

Let's try to write the nested query that solves this from lecture:

use ANY to write it.

use join to write it.

🏠 Portal | 🚀 Getting Started | 📝 SQL Cheat Sheet