SQL is a database language allows;

• create of database & table structures
• perform basic data management tasks (add, delete & modify)
• perform complex queries designed to transform the raw data into useful information

SQL functions fit into two broad categories;

• data definition language (DDL) - commands to create database objects such as tables, indexes, views, and define access rights to those database objects
• data manipulation language (DML) - commands to insert, update, delete, and retrieve data within the database tables

Database model - to illustrate the commands

A schema is a logical group of database objects

• such as tables and indexes - that are related to each other. Usually, the schema belongs to a single user or application

A single database can hold multiple schemas that belong to different users or applications

Schemas usefulness;

• group tables by owner (or function)
• enforce a first level of security by allowing each user to see only the tables that belong to that user

Authentication is the process the DBMS uses to verify that only registered users access the database

Create Database

• CREATE myDB

Create a database schema

• CREATE SCHEMA AUTHORIZATION {creator};

Create Tables

• CREATE TABLE tablename ( column1 data type [constraint] [,column2 data type [constraint] ][,PRIMARY KEY (column1 [, column2]) ] [,FOREIGN KEY (column1 [, column2]) REFERENCES tablename] [,CONSTRAINT constraint ] );

---



• CREATE TABLE VENDOR ( V_CODE INTEGER NOT NULL UNIQUE, V_NAME VARCHAR(35) NOT NULL,V_CONTACT VARCHAR(25) NOT NULL, V_AREACODE CHAR(3) NOT NULL, V_PHONE CHAR(8) NOT NULL, V_STATE CHAR(2) NOT NULL,V_ORDER CHAR(1) NOT NULL, PRIMARY KEY (V_CODE));

---



• CREATE TABLE PRODUCT ( P_CODE VARCHAR(10) NOT NULL UNIQUE, P_DESCRIPT VARCHAR(35) NOT NULL,P_INDATE DATE NOT NULL, P_QOH SMALLINT NOT NULL, P_MIN SMALLINT NOT NULL, P_PRICE NUMBER(8,2) NOT NULL, P_DISCOUNT NUMBER(5,2) NOT NULL, V_CODE INTEGER,PRIMARY KEY (P_CODE), FOREIGN KEY (V_CODE) REFERENCES VENDOR ON UPDATE CASCADE);

Create Tables

• CREATE TABLE CUSTOMER ( CUS_CODE NUMBER PRIMARY KEY, CUS_LNAME VARCHAR(15) NOT NULL, CUS_FNAME VARCHAR(15) NOT NULL, CUS_INITIAL CHAR(1), CUS_AREACODE CHAR(3) DEFAULT '615' NOT NULLCHECK(CUS_AREACODE IN('615','713','931')), CUS_PHONE CHAR(8) NOT NULL, CUS_BALANCE NUMBER(9,2) DEFAULT 0.00,CONSTRAINT CUS_UI1 UNIQUE (CUS_LNAME, CUS_FNAME));

---



• CREATE TABLE INVOICE ( INV_NUMBER NUMBER PRIMARY KEY, CUS_CODE NUMBER NOT NULL REFERENCES CUSTOMER(CUS_CODE), INV_DATE DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT INV_CK1 CHECK (INV_DATE > TO_DATE('01-JAN-2016','DD-MON-YYYY')));

---



• CREATE TABLE LINE ( INV_NUMBER NUMBER NOT NULL, LINE_NUMBER NUMBER(2,0) NOT NULL, P_CODE VARCHAR(10) NOT NULL, LINE_UNITS NUMBER(9,2) DEFAULT 0.00 NOT NULL, LINE_PRICE NUMBER(9,2) DEFAULT 0.00 NOT NULL, PRIMARY KEY (INV_NUMBER, LINE_NUMBER), FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ON DELETE CASCADE, FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE), CONSTRAINT LINE_UI1 UNIQUE(INV_NUMBER, P_CODE));

Indexes can be used to improve the efficiency of searches and to avoid duplicate column values

• CREATE [UNIQUE]INDEX indexname ON tablename(column1 [, column2])
• CREATE INDEX P_INDATEX ON PRODUCT(P_INDATE);
• CREATE UNIQUE INDEX P_CODEX ON PRODUCT(P_CODE);
• CREATE UNIQUE INDEX EMP_TESTDEX ON TEST(EMP_NUM, TEST_CODE,TEST_DATE);
• CREATE INDEX PROD_PRICEX ON PRODUCT(P_PRICE DESC);

To delete an index

• DROP INDEX indexname
• DROP INDEX PROD_PRICEX;

Adding Table Rows

• INSERT INTO tablename VALUES (value1, value2, ..., valuen)
• INSERT INTO VENDOR VALUES (21225,'Bryson, Inc.','Smithson','615','223-3234','TN','Y');
• INSERT INTO VENDOR VALUES (21226,'Superloo, Inc.','Flushing','904','215-8995','FL','N');
• INSERT INTO PRODUCT VALUES ('11QER/31','Power painter, 15 psi., 3-nozzle','03-Nov- 15',8,5,109.99,0.00,25595);
• INSERT INTO PRODUCT VALUES ('13-Q2/P2','7.25-in. pwr. saw blade','13-Dec-15',32,15,14.99, 0.05,21344);
• INSERT INTO PRODUCT VALUES ('BRT-345','Titanium drill bit','18-Oct-15', 75, 10, 4.50, 0.06, NULL);

Listing Table Rows

• SELECT columnlist FROM tablename
• SELECT * FROM PRODUCT;

Updating Table Rows

• UPDATE tablename SET columnname = expression [, columnname = expression][WHERE conditionlist ];
• UPDATE PRODUCT SET P_INDATE = '18-JAN-2016‘ WHERE P_CODE = '13-Q2/P2';
• UPDATE PRODUCT SET P_INDATE = '18-JAN-2016', P_PRICE = 17.99, P_MIN = 10 WHERE P_CODE = '13-Q2/P2';

Restoring Table Contents

• ROLLBACK;
• COMMIT and ROLLBACK work only with data manipulation commands that add, modify, or delete table rows

Deleting Table Rows

• DELETE FROM tablename [WHERE conditionlist ];
• DELETE FROM PRODUCT WHERE P_CODE = 'BRT-345';
• DELETE FROM PRODUCT WHERE P_MIN = 5;

Queries about this Lesson, please send them to: jmwaura@jkuat.ac.ke

*References*

• Database Systems: Design, Implementation, and Management, 12th ed. Carlos Coronel & Steven Morris
• Database Modeling and Design; Logical Design, 5th ed. Taby Teorey et.al
• Fundamentals of database systems, 6th ed. Ramez Elmasri & Shamkant B. Navathe