SQL has several parts: Major ones: DDL – Data Definition Language

1. SQL has several parts: Major ones: DDL – Data Definition Language {Defining, Deleting, Modifying relation schemas} DML – Data Manipulation Language {Inserting, Deleting, Modifying tuples in database} Embedded SQL – defines how SQL statements can be used with general-purposed programming

2. SQL For help: shell> mysql --help Using SQL: On any solaris/linux you have to use this to log on to MySQL: shell> /usr/local/mysql/bin/mysql -h faure -D loginname -p Basic Query: select A1, A2,…,An from r1, r2, …,rm where P A1, A2,…,An represent attributes r1, r2, …rm represent relations P represents predicate (guard condition)

3. Representations: PromptMeaning mysql> Ready for new command. -> Waiting for next line of multiple-line command. ‘> Waiting for next line, collecting a string that begins with a single quote (` ’ ’). “> Waiting for next line, collecting a string that begins with a double quote (` ” ’). mysql> SELECT * -> FROM my_table -> WHERE name = “Smith” AND age < 30; mysql> SELECT * FROM my_table WHERE name = “Smith” AND age < 30; \c to cancel the execution of a command Keywords may be entered in any letter case: mysql> SELECT VERSION(), CURRENT_DATE; mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE; mysql> SELECT SIN(PI()/4), (4+1)*5;

4. Creating and Using a Database • mysql> SHOW DATABASES; • SHOW statement can be used to find out the databases currently existing on the server • mysql> USE testdb • testdb is a database name. • USE command does not need a semi colon and must be given in a single line. • Database needs to be invoked in order to use it. • mysql> CREATE DATABASE example; • Database names are case-sensitive unlike keywords; Same applies for table names • So example != Example != EXAMPLE or some other variant

5. Creating a Table mysql> SHOW TABLES; Displays the current list of tables mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); mysql> SHOW TABLES; Will display the table with the table name pet Verification of the table can be done with DESCRIBE command mysql> DESCRIBE pet; +---------------- +---------------- +---------------- +---------------- +---------------- +---------------- + | Field | Type | Null | Key | Default | Extra | +---------------- +---------------- +---------------- +---------------- +--------- +---------------- + | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------------- +---------------- +---------------- +---------------- +---------------- +---------------- +

6. Loading Data into a Table LOAD DATA uses a text file with single record in a line that match the attributes in the table. Useful for inserting when multiple records are involved. Example: pet.txt is a text file with a single record Name ownerspeciessexbirthdeath Whistler Gwenbird\N1997-12-09\N mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet; INSERT command can be used when records needs to be inserted one at a time. NULL can be directly inserted in the field column Example: mysql> INSERT INTO pet -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL); Using User Variables select @min_price:=min(price),@max_price:=max(price) from shop; select * from shop where price=@min_price or price=@max_price;

7. Examples of some common queries CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT ‘0000' NOT NULL, dealer CHAR(20) DEFAULT ‘’ NOT NULL, price DOUBLE(16,2) DEFAULT '0.00’ NOT NULL, PRIMARY KEY(article, dealer)); INSERT INTO shop VALUES (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69), (3,'D',1.25),(4,'D',19.95); mysql> SELECT * FROM shop; +--------------+--------+---------+ | article | dealer | price | +--------------+--------+---------+ | 0001 | A | 3.45 | | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | B | 1.45 | | 0003 | C | 1.69 | | 0003 | D | 1.25 | | 0004 | D | 19.95 | +--------------+---------+-------+ The maximum value for a column The row holding the maximum of a certain column Maximum of column per group The rows holding the group-wise maximum of a certain field

8. SELECT MAX(article) AS article FROM shop • SELECT article, dealer, price • FROM shop • WHERE price=(SELECT MAX(price) FROM shop) • (or) • Get the maximum price value from the table with a SELECT statement. • Using this value compile the actual query: • SELECT article, dealer, price • FROM shop • WHERE price=19.95 • SELECT article, MAX(price) AS price • FROM shop • GROUP BY article • SELECT article, dealer, price • FROM shop s1 • WHERE price=(SELECT MAX(s2.price) • FROM shop s2 • WHERE s1.article = s2.article);