SQL Exercises Part I

1
SQL Exercises Part I
October 9, 2009
2
Some Simple SQL Commands

• CREATE TABLE test
• ( i int,    s char(10)      )
• DESCRIBE test

3
Inserting tuples into table

• INSERT INTO test VALUES(10, 'foobar1')
• INSERT INTO test VALUES(11, 'foobar2')
• INSERT INTO test VALUES(12, 'foobar3')

4
Querying the table

• SELECT FROM test
• SELECT FROM test where i10
• SELECT FROM test where sfoo
• SELECT FROM test
• where s like fo

5
Deleting tuples from table

• Delete from test where i10
• Delete from test
• DROP TABLE test

6
Exercise 1

• MovieExec (name, address, cert, netWorth)
• Studio (name, address, precsC)
• Describe the tuples that would appear in the
  following expression
• Studio CROSS JOIN MovieExec

7
Solution 1 - 1

• Q Studio CROSS JOIN MovieExec
• A The result will be a 7-column relation with
  all the attributes of Studio and MovieExec. Every
  pair consisting of one tuple of Studio and one
  tuple of MovieExec will be a tuple of the
  resulting relation

8
Solution 1 - 2
Instance Studio

9
Exercise 2 - 1

• Movie(title, year,length, inColor)
• StarsIn(movieTitle, movieYear, starName)
• MovieStar(name, address, gender, birthdate,
  income)
• Q-1 Find Harrison Fords birth date.

10
Solution 2 - 1

• Q-1 Find Harrison Fords birth date.
• Select birthdate
• From MovieStar
• Where nameHarrison Ford

11
Exercise 2 - 2

• Movie(title, year,length, inColor)
• StarsIn(movieTitle, movieYear, starName)
• MovieStar(name, address, gender, birthdate,
  income)
• Q-2 Find all the movie stars who earn at least
  10,000,000

12
Solution 2 - 2

• Q-2 Find all the movie stars who earn at least
  10,000,000
• Select name
• From MovieStar
• Where incomegt 10,000,000

13
Exercise 2 - 3

• Movie(title, year,length, inColor)
• StarsIn(movieTitle, movieYear, starName)
• MovieStar(name, address, gender, birthdate,
  income)
• Q-3 Find all the stars who either are male or
  live in Montreal (have string Montreal as part of
  their address)

14
Solution 2 - 3

• Q-3 Find all the stars who either are male or
  live in Montreal (i.e. have string Montreal as
  part of their address)
• Select name
• From MovieStar
• Where genderM or
• address like Montreal

15
Exercise 2 - 4

• Movie(title, year,length, inColor)
• StarsIn(movieTitle, movieYear, starName)
• MovieStar(name, address, gender, birthdate,
  income)
• Q-4 Find all the color movies (i.e.
  inColorcolor) that were made in 1980 and have
  length more than 80 minutes.

16
Solution 2 - 4

• Q-4 Find all the color movies (i.e.
  inColorcolor) that were made in 1980 and have
  length more than 80 minutes.
• Select title
• From Movie
• Where inColorcolor and
• year1980 and
• lengthgt80

17
Exercise 2 - 5

• Movie(title, year,length, inColor)
• StarsIn(movieTitle, movieYear, starName)
• MovieStar(name, address, gender, birthdate,
  income)
• Q-5 Find all the color movies that Harrison Ford
  has played.

18
Solution 2 - 5

• Q-5 Find all the color movies that Harrison Ford
  has played
• Select title
• From Movie, StarsIn
• Where titlemovieTitle and
• yearmovieYear and
• starName Harrison Ford and
• inColor color

19
Exercise 3

• Create an Employee table that can be used to
  store information related to employees first
  name, last name, SIN, employee number, birthdate,
  address, gender, and salary.

20
Solution 3

• CREATE TABLE EMPLOYEE
• (FirstName VARCHAR(15) NOT NULL,
• LastName VARCHAR(15) NOT NULL,
• SIN CHAR(9) NOT NULL,
• EmployeeNum CHAR(12) NOT NULL,
• BirthDate DATE,
• Address VARCHAR(30),
• Gender CHAR,
• Salary DECIMAL(10,2),
• PRIMARY KEY (SIN),
• UNIQUE (EmployeeNum ))