IS371 Lab 2

Practise with basic SQL using SQLite

Getting Started

Make sure you have correctly installed SQLite add-on on your machine. A description is found here.


Create a directory on your machine for holding the IS371 lab session files, say C:\IS371 or similar. Then download this zip file and unpack it in that directory. This should create a subdirectory IS371_lab1 containing six files.

Open SQLite GUI:

SQLite

Uploading tables

From the toolbar select Database > Import Select the files you just downloaded. You can select the file using Select File button.


Very Important! You should Tick the option "First row contains column names".
This is in order to have the coulumn names correctly set.

SQLite_Alert
Select OK button.


SQLite_Alert
Select OK button.


Please insert the data type for each column
SQLite_Alert

according to the following figure:

SQLite_Alert
Select OK button.



Success! You should repeat this process untill you have all 6 tables successfully uploaded.

SQLite_Alert

Queries

Try to find the correct SQL statement for each of the following Queries:

  1. Retrieve the birthdate and address of the employee(s) whose last name is king
  2. SELECT bdate, address FROM employee WHERE lname = 'King'


  3. Retrieve the first and last name of the employee(s) whose middle initial is A
  4. SELECT fname, lname FROM employee WHERE minit = 'A'


  5. Retrieve departments names and first and last names of department's managers
  6. SELECT dname, fname, lname FROM employee, department WHERE ssn=mgrssn


  7. Retrieve first and last names of all married employees
  8. Hint: a married employee should have a spouse

    SELECT fname, lname FROM employee, dependent WHERE essn=ssn AND relationship=='Spouse'


  9. Retrieve all departments (name and number) located in Houston
  10. SELECT dname, department.dnumber FROM department, dept_locations WHERE department.dnumber= dept_locations.dnumber AND dlocation='Houston'


  11. Retrieve the employee's first and last name for each employee whos immediate supervisor last name is king


  12. List the first name, last name, and ID for each supervisor without duplication
  13. Hint: you maight need to use DISTINCT here


Please use the following button to submit your SQL statements