CSCI 370
Introduction to Databases
02.02 Introduction to SQL
1
Intro to SQL
◦ A principle language for relational DB.
◦ Data Definition Language: declaring DB schemas.
◦ Data Manipulation Language: queries and modifications to DB.
◦ We are assuming the SQL-99 standard.
◦ Just like other programming languages like Java or C++, but it is used
exclusively in the DB domain
◦ Another difference: SQL is declarative in nature, neither procedural nor
object-oriented.
2
Relations in SQL
◦ Relations -> Tables
◦ Data -> Tuples in the Tables
◦ Views -> relations defined by a computation, constructed but not stored.
(more on this later)
◦ Temporary tables -> holding query results, discarded afterwards (not
stored).
3
Creating a Table in SQL
4
SQL Data Types - Strings
◦ CHAR(n): fixed-length string of up to n chars. (implementation-dependent,
padded or not)
◦ VARCHAR(n): variable length string of up to n chars.
◦ BIT(n): fixed-length bit string. (BIT(1) sometimes as just BIT)
◦ BIT VARYING(n): variable-length bit string.
5
SQL Data Types - Numerical
◦ Integer types: INT, INTEGER, SHORTINT
◦ Floating-point types: FLOAT (REAL), DOUBLE PRECISION
◦ DECIMAL(n,d): n total digits, d digits after the decimal point
◦ E.g., 1234.56 is of DECIMAL(6,2) type
◦ Date and Time: char strings of special forms.
◦ DATE: Date ‘YYYY-MM-DD’, E.g., DATE ‘1948-05-14’
◦ TIME: TIME ‘HH:MM:SS.F’, E.g., TIME ’15:00:02.5’
◦ BOOLEAN: not normally used, use BIT instead.
◦ Three Values: TRUE, FALSE, and UNKNOWN! (More on this later)
6
Creating another Table in SQL
7
Modify Tables
◦ Deletion: DROP TABLE R;
◦ E.g., DROP TABLE MovieStar;
◦ Add an attribute to a Table: ALTER TABLE R ADD ATRN DTYPE;
◦ E.g., ALTER TABLE MovieStar ADD phone CHAR(16);
◦ Delete an attribute from a Table: ALTER TABLE R DROP ATRN;
◦ E.g., ALTER TABLE MovieStar DROP gender;
8
Default Values
◦ Set a default value to an attribute: use DEFAULT $Value$
◦ E.g., gender VARCHAR(1) DEFAULT ‘?’,
◦ E.g., birthdate DATE DEFAULT DATE ‘0000-00-00’,
◦ E.g., ALTER TABLE MovieStar ADD Phone VARCHAR(16) DEFAULT ‘unlisted’;
9
Declaring Keys
◦ Use PRIMARY KEY or UNIQUE keywords
◦ Both do not allow duplicate values, but UNIQUE accepts NULL
value, while PRIMARY KEY does not
◦ A table can only has one PRIMARY KEY
◦ option 1 (replacing PRIMARY KEY with Unique also works)
10
Declaring Keys Cont.
◦ Use PRIMARY KEY or UNIQUE keywords
◦ option 2 (replacing PRIMARY KEY with Unique also works)
11
Exercise 1
◦ 1) Write a suitable schema for relation Product in SQL.
12
Exercise 2
◦ 2) Write a suitable schema for relation Product in PC.
13
Exercise 3
◦ 3) Write a suitable schema for relation Product in Laptop.
14
Exercise 4
◦ 4) Write a suitable schema for relation Product in Printer.
15
Exercise 5
◦ 5) Write an alteration (SQL) to the Printer Schema to delete the
attribute color.
16
Exercise 5
◦ 6) Write an alteration (SQL) to the Laptop Schema to add the attribute
od (optical-disk type, e.g., cd or dvd). Let the default value to be ‘none’.
17
Activity 1
◦ 1) Write a suitable schema for Classes.
18
Activity 1
◦ 2) Write a suitable schema for Ships.
19
Activity 1
◦ 3) Write a suitable schema for Battles.
20
Activity 1
◦ 4) Write a suitable schema for Outcomes.
21
Activity 1
◦ 5) Write an alteration to Classes to delete the attribute bore.
22
Activity 1
◦ 6) Write an alteration to Ships to include the attribute yard giving the
shipyard where the ship was built.
23
Activity 2
◦ Produce appropriate schemas (SQL)
24
