CS105
LAB 2-Intro to SQL
Objectives
  1. Get to know the SQLite environment
  2. Creating Tables
  3. Take a look at a dataset
  4. Bulk Load of data
  5. Selection Queries
  6. Inserting rows in a table
  7. Dropping Tables

1. Get to know the SQLite environment

For the purposes of our lab we will use the sqlite3 . In the lab we use windows. For Mac OS and Linux, you can dowload binaries here.

  1. Download here
  2. Open Z:drive
  3. Create a directory there named "db"
  4. Unzip the downloaded program in that directory (Z:/db)
  5. Comment: We do this once: Z drive is accessible from all computers.
  6. Do Start->Run
  7. Hit "cmd" - enter
  8. Write "cd z:\db".
  9. Comment: cd stands for "change directory" and z:\db for the new directory location.
  10. Write "sqlite3 mydata.db"
  11. Comment: sqlite3 calls the db-program and mydata.db is the database that we will work with.
  12. You should now seethe SQL prompt :
    sqlite>
  13. This is a program in the Command-Window. You are now ready to write sql commands!

But before that:

  1. .h : Help (saves time, very useful)
  2. .mode column : aligns query results
  3. .headers ON : puts attribute names as headers in query result table
  4. .exit : exits

These were non-SQL commands. Remember that SQL command ends with a semi-colon ";"

Detailed Online Documentation can be found at: http://www.sqlite.org/sqlite.html

2. Creating Tables

In the classroom we talked about the University Domain. Let's create the appropriate tables. The tables are:

Student(id, name)
Department(name, office)
Room(id, name, capacity)
Course(name, start, end, room)
MajorsIn(student, dept)
Enrolled(student, course, credit_status)

Remember to:

  1. use appropriate types
  2. provide primary keys
  3. enforce referential integrity using foreign keys where appropriate
  4. use NOT NULL and UNIQUE where appropriate

Here is something to start with:

CREATE TABLE Student(id CHAR(8) PRIMARY KEY,name VARCHAR(30) NOT NULL);

CREATE TABLE Department(name VARCHAR(30) PRIMARY KEY, office VARCHAR(30) NOT NULL);

...

CREATE TABLE MajorsIn(student CHAR(8), dept VARCHAR(30), PRIMARY KEY (student,dept),
FOREIGN KEY(student) REFERENCES Student(id),
FOREIGN KEY(dept) REFERENCES Department(name));

...

Here is a script, save it to the z:\db directory and run it using
>.read "create.txt"

Notes:

  1. Always check the output thoroughly for ERRORs!!
  2. You can list your tables using ".tables" . Check if all 6 tables are there!
  3. You can see how a table was created using: ".schema some_table_name"!

3. Bulk Load of Data

Bulk loading data in a database is extremely useful. We will load the tables of university database from existing files. Before bulk load, your tables should be created on psql environment (i.e.. by using create table commands as in previous part). Since we have already created the tables, now we will load data.

Now, we will load the data into the tables of university database.

  1. Copy there the following files: course.in, department.in, enrolled.in, majorsin.in, student.in, room.in in z:\db
  2. Issue the following commands:
    .separator "\t"
    .import "student.in" student
    .import "course.in" course
    .import "room.in" room
    .import "department.in" department
    .import "enrolled.in" enrolled
    .import "majorsin.in" majorsin
  3. Great!! Your database should look somewhat like this (records are not exactly the same!)

4. Simple Selection Queries

1. Retrieving whole table

A query result is always in the form of the table. Try the following query:

SELECT *
FROM Enrolled;

2. Retrieve specific columns

If you only need some of the attributes, you should specify them after SELECT. For example below query retrieves credit_status column of Enrolled table.

SELECT credit_status
FROM enrolled;

List the names of the students from student table.

3. Retrieve specific rows.

Write a query for retrieving all students major in comp sci. Now we should use WHERE clause in order to specify the property of rows, which we want to retrieve.

SELECT *
FROM MajorsIn
WHERE dept = 'comp sci';

Write a query listing the ID of the room that course cs105 is taught

4. Aggregate queries

Now try the following query:

SELECT COUNT(*)
FROM MajorsIn
WHERE dept = 'comp sci';

What is the meaning of the output?

Write a query that calculates the average, the sum, the min and the max capacity of the rooms.

5. Subqueries

Following query lists the name of the students who do not majors in comp sci

SELECT name
FROM Student
WHERE id NOT IN (SELECT student
                 FROM MajorsIn
                 WHERE dept = 'Comp Sci');

Write a query listing the name(s) of the room(s), with the greatest capacity.

5. Inserting rows in a table

Let's insert some records:

Try :

INSERT INTO Student VALUES('00111100','Paul Hewson');
INSERT INTO Student VALUES('11000011','David Evans');
INSERT INTO Student VALUES('00001111','Adam Clayton');
INSERT INTO Student VALUES('11110000','Larry Mullen Jr');

INSERT INTO Department VALUES('CS','CAS100');
INSERT INTO Department VALUES('MATH','CAS200');
INSERT INTO Department VALUES('MUS','MUS300');
INSERT INTO Department VALUES('POL','CAS400');

INSERT INTO MajorsIn VALUES('00111100','MUS');
INSERT INTO MajorsIn VALUES('11000011','MUS');
INSERT INTO MajorsIn VALUES('00111100','POL');

 

Here is a script for these insertions:

insertions.txt

You can retrieve these records by setting a simple query:

SELECT *
FROM relation;

6. Dropping Tables

Try:

drop table enrolled;

drop table department;

drop table course;

drop table room;

drop table student;

drop table majorsin;

CS105