CSC3530 Software Technology

CSC3530 Software Technology Tutorial 4 SQL

SQL / RDBMS • Structured Query Language • 4th Generation programming language • Tell computer what to do instead of how to do • An precise way to manipulate data • SQL statement • update • insert • select • RDBMS – Relation DataBase Management System • Oracle • mySQL • A system that store data in an efficient way that allow us to retrieve and update by issuing SQL statement

Oracle Account • Login to your unix account • The oracle account password is same as your unix account • Login to oracle server • Type> source /usr/local/oracle8i/setup • Append “source /usr/local/orale8i/setup” in .cshrc file • Type> sqlplus • Input account and password • cprjxxxx@db00 • Password given in e-mail • You can start executing SQL statement • Supp. notes from course CSC3170 Fall 1999 • http://www.cse.cuhk.edu.hk/~csc3530/sqlsup.pdf

Database Schema • staff(code, name, phone, email, password, rank) • Underline means key • Generic form • Table_name (field_name1, field_name2, …)

Create Table create table staff ( code char(10) not null, name varchar2(50), phone char(8), email varchar2(50), password varchar2(10), rank varchar2(20), primary key (code) ); • Table name – staff • char(10) – fixed length string (length = 10) • varchar2(50) – variable length string (max. length = 50) • not null – cannot be missing • primary key – indicate code is unique to differentiate different records

Cont’d • Other data type • int or integer • numeric(p,d) – totally p digits, with d digits on the right of decimal point • float(n) – floating point number with at least n digits • date – date in (4 digit) year, month, day • time – time in hours, minutes and seconds • Altering table that already created • alter table staff drop phone • alter table staff add phone varchar2(20)

Inserting/Deleting Records insert into staff (code, name, phone, email, password, rank) values (‘test’, ‘test’, ‘12345678’, ‘t@test’, ‘1234’, ‘Manager’); • string are embraced by single quote • Integer does not need to be embraced • Syntax: insert into “table_name”(“fields”) values (“fields”); delete staff where name=‘Vernon’; • The row with name equals Vernon is drop • Syntax : delete “table_name” where “condition”;

Updating Records • Suppose you issued an invoice, you have to subtract the items you sold from the stock update product set on_hand=on_hand-5 where code=‘B2345’; • Suppose a staff change his password Update staff set password=‘newpassword’ where code=‘staffid’; • Suppose an invoice need to be voided Update invoice set voided=‘true’ where invoice_number=‘abcde123’;

Selecting Records Product(code, category, name, on-hand, low-limit) select code,name from product where on_hand < low_limit; Syntax: select “field_name” from “table_name” where “condition”;

Joint Table Query • Sometimes you need to do complex query • Find those suppliers that supply us the product which are out of stock now select S.name from supplier S, product P, supplying SU where S.code=SU.supplier_code and SU.product_code=P.code and P.on_hand < P.low_limit; • If you don’t use join table, you may • Find product-code that is out of stock • Find the supplier-code that supply that product-code • Find the name of supplier with that supplier-code

Nested Query / Partial Match • Instead of join table, you can make nested query select S.name from supplier S where S.code in (select SU.supplier_code from supplying SU where SU.product_code in (select P.code from product P where P.on_hand<P.low_limit)); • You want to find all product which name consist of “er” • % here means any character select * from product where name like ‘%er%’;

Beyond The Basics • Adding constraint for a table, if violated, error will occur create table product ( code char(10) not null, category varchar2(50), name varchar2(50), on_hand integer, low_limit integer, primary key (code), check (on_hand >= 0)); • When you issue invoice to sell an item, you need to deduct it from the product table, error will occur if you make the on_hand < 0, so …

Cont’d • Find the best selling product’s product_code select I.product_code from invoice_item I group by I.product_code having sum (I.qty*I.price) >=all (select sum(I1.qty*I1.price) from invoice_item I1 group by I1.product_code); • To interpret • First, find the sales figure of each product • Second, find the product which sales figure is highest • Rank the product by their sales figure select I.product_code, sum(I.qty*I.price) as sales from invoice_item I group by (I.product_code) order by sales desc;

OLAP • OLTP – On line Transaction Processing • Support daily transaction of a company • Sales in the outlet • Recording working hours of staff • Mainly consist of update to a single table • OLAP – On line Analytical Processing • Decision support for an enterprise • The trend of a product base on its sales figure • How is the utilization of staff in a company • Mainly consist of join table query (we call it view) • The two query introduced previously is a simple kind of OLAP

CSC3530 Software Technology