1 / 11

CSI 2 5 32 Lab 2

CSI 2 5 32 Lab 2. 20 Janvier 2012 Programmation SQL. Sommaire. Réviser la syntaxe SQL de CREATE TABLE INSERT SELECT Exercice: Créer des tables à partir d’un ER Insérer des données dans les tables Faire des requêtes à la DB. CREATE TABLE. Syntaxe: CREATE TABLE TableName (

kimberly-woods
Download Presentation

CSI 2 5 32 Lab 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CSI 2532 Lab2 20 Janvier 2012 Programmation SQL

  2. Sommaire • Réviser la syntaxe SQL de • CREATE TABLE • INSERT • SELECT • Exercice: • Créer des tables à partir d’un ER • Insérer des données dans les tables • Faire des requêtes à la DB

  3. CREATE TABLE • Syntaxe: CREATE TABLE TableName ( attributeName1 type1, attributeName2 type2, … attributeNameN typeN, Constraint1, Constraint2, …, ConstraintM ) • Exemple CREATE TABLE Artist ( AName VARCHAR(20), Birthplace VARCHAR(20), Style VARCHAR(20), DateOfBirth DATE, PRIMARY KEY (AName) );

  4. Créer les tables Artist, Artwork, Customer, LikeArtist Rappelez-vous de créer les contraintes Primary key et Foreign key Une bonne convention est de mettre les PKs et FKs en premier. Ouvrez pgAdmin et naviguez vers Tools  Query Tool. ER Diagram

  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) FOREIGN KEY (CustId) ); REFERENCES Customer);

  7. Insertion • Syntaxe: INSERT INTO TableName(attrName1,…,attrNameN) VALUES (Value1,…, ValueN); • Exemple: INSERT INTO Artist(AName,BirthPlace,Style,DateOfBirth) VALUES (‘Caravaggio’,’Milan’,’Baroque’,’1571-09-28’ );

  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): • Notez que AName est une FK, donc sa valeur doit exister dans la table Artist. • (‘Blue’, 2000,‘Modern’,10000.00,‘Smith’) • (‘The Cardsharps’, 1594,40000.00,’Caravaggio’)

  9. Simple SELECT query • Syntaxe SELECT attr1,att2,…,attrN FROM table1,table2,…,tableM WHERE <conditions> • Exemple SELECT Style FROM Artist WHERE AName = 'Smith'; ou SELECT A.Style FROM Artist A WHERE A.AName = 'Smith';

  10. Exercices! • Listez tous les artistes qui sont nés à Ottawa • Listez les titres et les prix de tous les oeuvres peints en 2000.

  11. Pour plus d’informations! • Sur la syntaxe SQL: • http://www.faqs.org/docs/ppbook/c22759.htm • PostgreSQL Documents: • Chapter 5. Data Definition • http://www.postgresql.org/docs/8.3/static/ddl.html • Chapter 6. Data Manipulation • http://www.postgresql.org/docs/8.3/static/dml.html • Chapter 7. Queries • http://www.postgresql.org/docs/8.3/static/queries.html

More Related