CSI 2132 Lab2

1
CSI 2132 Lab2

• Basic SQL Programming
• Arif Emre Caglar

2
Outline

• Review the syntax of
• CREATE TABLE
• INSERT
• SELECT
• Exercise
• Creating tables from ER Diagrams
• Inserting Data to tables
• Querying the database
• Download this PPT from
• http//www.site.uottawa.ca/acagl079/labs08.htm

3
CREATE TABLE

• Syntax
• CREATE TABLE TableName
• (
• attributeName1 type1,
• attributeName2 type2,
• attributeNameN typeN,
• Constraint1, Constraint2, , ConstraintM
• )
• Example
• CREATE TABLE Artist
• (
• AName VARCHAR(20),
• Birthplace VARCHAR(20),
• Style VARCHAR(20),
• DateOfBirth DATE,
• PRIMARY KEY (AName)

4
ER Diagram

• Create tables Artist, Artwork, Customer,
  LikeArtist
• Remember the Primary key and Foreign key
  constraints.
• A good convention is declaring the Primary Key(s)
  as the first attribute(s) in the tables
• Open pgAdmin, login to your database, and then
  navigate Tools ? Query Tool.

5
Your Task Creating the Four Tables

• Some Useful Data Types
• VARCHAR(n)
• DATE
• NUMERIC(n,m)
• INTEGER

6
The Code for All the Tables

• CREATE TABLE Artist CREATE TABLE Artwork
• ( (
• AName VARCHAR(20), Title VARCHAR(20),
• Birthplace VARCHAR(20), Year
  INTEGER,
• Style VARCHAR(20), Type VARCHAR(20),
• DateOfBirth DATE, Price NUMERIC(8,2),
• PRIMARY KEY (AName) AName
  VARCHAR(20),
• PRIMARY KEY (Title),
• ) FOREIGN KEY(AName)
• REFERENCES Artist)
• CREATE TABLE LikeArtist CREATE TABLE Customer
• ( (
• CustId INTEGER, CustId INTEGER,
• Aname VARCHAR(20),
  Name VARCHAR(20),
• PRIMARY KEY(Aname, CustId), Address
  VARCHAR(20),
• FOREIGN KEY (Aname) Amount NUMERIC(8,2),
• REFERENCES Artist, PRIMARY KEY
  (CustId)

7
Insertion

• Syntax
• INSERT INTO TableName(attrName1,,attrNameN)
• VALUES (Value1,, ValueN)
• Example
• INSERT INTO Artist(AName,BirthPlace,Style,DateOfBi
  rth)
• VALUES (Caravaggio,Milan,Baroqu
  e,1571-09-28 )
• Character values are quoted by , and numerical
  values are unquoted when inserting.
• Several inserts can be done consecutively in
  Query Tool.

8
Insert the following values

• InTo Table Artist(AName,Birthplace, Style,
  DateOfBirth)
• (Caravaggio,Milan,Baroque,1571-09-28 )
• (Smith, Ottawa, Modern, 1977-12-12)
• (Picasso,Malaga,Cubism,1881-10-25)
• InTo Table Artwork(Title,Year,Type,Price,AName)
• (Note that AName is a foreign key, value should
  exist in Artist)
• (Blue, 2000,Modern,10000.00,Smith)
• (The Cardsharps, 1594,40000.00,Caravaggio)

9
Simple SELECT query

• Syntax
• SELECT attr1,att2,,attrN
• FROM table1,table2,,tableM
• WHERE ltconditionsgt
• Example
• SELECT Style FROM Artist WHERE AName 'Smith'
• or
• SELECT A.Style FROM Artist A WHERE A.AName
  'Smith'

10
Simple Exercises

• List all artists that are born in Ottawa
• List the titles and prices of all artworks
  painted in 2000.

11
For Detailed Information

• About Syntax
• http//www.faqs.org/docs/ppbook/c22759.htm

12
My Contact

• Arif Emre Caglar
• Email acagl079_at_uottawa.ca
• Office SITE 4023
• Office Hours Tuesday 1500 1600