Example queries

(1) CREATE TABLE b( b integer NOT NULL, name2 VARCHAR(5),

CONSTRAINT pk_b PRIMARY KEY (b)) WITH OIDS;

(2) CREATE TABLE a( a integer NOT NULL, name VARCHAR(10), b integer,

CONSTRAINT pk_a PRIMARY KEY (a),

CONSTRAINT fk_b FOREIGN KEY (b)

REFERENCES b (b) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION) WITH OIDS;

(3) INSERT INTO b VALUES(1,'aaa');

(4) INSERT INTO b(name2,b) VALUES('bbb',2);

(5) INSERT INTO b VALUES(3);

(6) INSERT INTO a VALUES(1,'AAAAA',1);

(7) INSERT INTO a(b,name,a) VALUES(3,'BBBBB',2);

(8) SELECT * FROM a,b WHERE a.b = b.b ORDER BY name2;

(9) SELECT * FROM a inner join b on a.b = b.b ORDER BY name2 DESC;

(10) SELECT * FROM a left join b on a.b = b.b;

(11) SELECT * FROM a right join b on a.b = b.b;

(12) SELECT * FROM a full join b on a.b = b.b;

(13) DROP TABLE IF EXISTS a,b;

Description:

·         Queries numbers 1 and 2 create two tables with fields and constraints;

·         Queries with numbers from 3 to 7 inclusively show different ways for adding data into the tables;

·         Queries from 8 to 12 can’t be used at one time; there are queries for selecting data from table. Test them and answer the question: Why do queries 8,9,10 show two tuples, while queries number 11 and 12 show three tuples?

·         Last query in this script drop tables “a” and “b”.

Exercises

If A = 6, B = 5. C = 4, state the truth values of the following expressions:

a. A = B OR ( B > C AND A > 0 )

b. A < C OR B < C OR NOT ( A = C )

c. NOT (A = B) AND NOT NOT B<>C

d. NOT (A=B OR B>C) AND NOT (C <> A)

e. (A+C+B > 2*B-C+A*8-B*9+C*5-(A+C)*B) OR TRUE

When this predicate has false values: (A > B AND B > C) OR C > A

laboratory operation

Subject: “An introduction to SQL”

Purpose: using SQL statement for creating databases structures and modifies theirs.

Task:

1.     Choose your universe of discourse and design objects in your database.

2.     Create schema that include tables with theirs fields and without constraints.

3.     Create scripts that add data in tables.

4.     Create View that demonstrates how works inner join.

5.     Create View that demonstrates how works left join.

6.     Create View that demonstrates how works right join.

7.     Create View that demonstrates how works full join.

ALL VIEWS MUST BE RETURNED DIFFERENT RESULTS

8.     Define constrains for tables (ALTER cause).

9.     Update part of tuples.

10.  Delete other part of tuples.

11.  Drop all tables.

<< К содержанию >>