SQL Commands: DDL and DML

• There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL

DDL Commands

• CREATE is used to create tables and has the syntax: CREATE TABLE table_name(Col_name1 datatype(), Col_name2 datatype(),... Col_namen datatype(), );
• An example of CREATE is: CREATE TABLE DDL ( id int, DDL_Type varchar(50), DDL_Value int);
• ALTER TABLE table_name ADD to add a column with the syntax ALTER TABLE table_name ADD Col_name datatype()...;
• Example: ALTER TABLE DDL ADD COLUMN DDL_Example varchar(50);
• ALTER TABLE table_name can DROP COLUMN column_name
• Example: ALTER TABLE DDL DROP COLUMN DDL_Example
• DROP TABLE table_name deletes a table
• TRUNCATE TABLE table_name deletes the data inside a table, name table keeps
• RENAME table table_name to new_table_name and renames tables
• Example: RENAME table DDL to DD;

DML Commands

• INSERT INTO Table_Name VALUES(); and inserts a row
• To insert into table DDL, use the command: INSERT INTO DDL (id, DDL_Type, DDL_Value) VALUES (2, 'DML', 123);
• UPDATE tablename set to(calculation); is used to update column
• Example syntax: UPDATE dd1 SET DDL_Value = 555 WHERE DDL_Type = 'DML';
• DELETE FROM table_name
• Example: DELETE FROM DDL;

DQL Commands

• SELECT column1, column2, ... FROM table_name WHERE condition; to grab certain columns
• To select all columns you can use SELECT * FROM Customers WHERE Country='Mexico';
• It is possible to select all columns where a condition is true with: SELECT * FROM Customers WHERE CustomerID > 80;

SQL Integrity Constraints

• SQL constraints specify rules for data in a table and limit the type of data that can go into the table
• NOT NULL ensures a column cannot have a NULL value
• UNIQUE ensures all values in a column are different
• PRIMARY KEY is a combination of NOT NULL and UNIQUE constraints.
• FOREIGN KEY prevents actions that would destroy links between tables
• CHECK ensures the values in a column satisfies a specific condition
• DEFAULT sets a default value for a column if no value is specified

NOT NULL example

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int );

UNIQUE example

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID));

PRIMARY KEY example

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID));

FOREIGN KEY example

• Foreign key creates a referencing relation to another table with CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons (PersonID));

CHECK example

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18) );

DEFAULT example

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes');

• CREATE TABLE Stud (ID int NOT NULL, Name varchar(255), Age int, Location varchar(255) DEFAULT 'Noida'); is an example with table Stud and default value Noida
• Inserting the VALUES (5, 'Hema', 27,DEFAULT); will set Location to Noida

Transaction Control Language

• TCL commands include: COMMIT, ROLLBACK, and SAVEPOINT
• UPDATE STUDENT SET NAME = 'Sherlock' WHERE STUDENT_NAME = ‘Jolly';
• Rollback allows for going back to a previous state
• SAVEPOINT allows the user to specify certain points

Set Operations

• Set operations are UNION, UNION ALL, INTERSECTION, MINUS

SQL Aggregate Functions

• Common aggregate functions in SQL include: MIN(), MAX(), SUM(), AVG(), and COUNT()
• An aggregate function in SQL returns one value after calculating multiple values of a column
• SELECT AVG (salary) FROM employees; returns the average from the salary column in table employees
• SELECT COUNT(empid) FROM employees; // ignore null value counts employee ids from employee, null values will be ignored
• SELECT COUNT(*) FROM employees; // count total no of rows counts total number of rows in employees

Rename

• Rename a table: ALTER TABLE table_name RENAME TO new_table_name;
• Example: ALTER TABLE std RENAME TO students;
• Rename an attribute: ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
• Example: ALTER TABLE student RENAME COLUMN sname TO name;

AS

• Renames CustomerID to ID SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;
• Grabs OrderID, OrderDate, and CustomerName from customer and order tables. SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;

SQL Keywords

• DISTINCT is used to return only distinct (different) values
• SELECT DISCTINCT (NAME) FROM Customers; returns the distinct names in Customers
• ORDER BY sorts the result-set in ascending or descending order
• SELECT * FROM Customers ORDER BY Country; orders the customers table by the country
• SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC; sorts CustomerName in descending order

LIKE (% and _)

• This filter SQL records on specific string matches
• SELECT first_name FROM employees WHERE first_name LIKE 'A%' (start with) This grabs first names fromm employess that start with A
• SELECT first_name FROM employees WHERE first_name LIKE 'Adam%' This grabs first names from employees that stat with Adam
• ELECT first_name FROM employees WHERE first_name LIKE '%ann' (ends with) This grabs first names from employees that end with ann
• SELECT first_name FROM employees WHERE first_name LIKE ' _ _ 'selects first_name from employees WHERE first_name LIKE ' _ _ '
• SELECT title FROM titles WHERE title NOT LIKE 'Staff' selects name that is not Staff
• SELECT first_name, last_name FROM employees WHERE first_name LIKE 'Z%' AND last_name LIKE 'Z%' selects names that START with Z

More LIKE Example

• SELECT * FROM Customers WHERE country LIKE 'U_'; selects country name that starts with U and is followed by one character
• 'SELECT * FROM Customers WHERE last_name LIKE 'R%t' OR last_name LIKE '%e'; selects last names that start with R, end with t, or end with e

BETWEEN

• This selects values within a given range
• SELECT * FROM Products WHERE Price BETWEEN 10 AND 20; selects all products with price between 10 and 20

GROUP BY

• This groups rows that have the same values into summary rows
• This is often used with aggregate functions
• SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country; groups CustomerID by country

HAVING

• WHERE keyword cannot be used with aggregate functions
• SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5; lists the number of customers in each country, but only if the country has more than 5 customers
• SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC; lists the number of customers, only in more than 5 in descending order

IN

• This specifies multiple values in the WHERE clause
• SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK'); Selects all customers that are located in Germany, France, or UK
• SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK'); Selects all customers that are not located in Germany, France, or UK
• SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers); selects Country from the Country column in suppliers

EXISTS

• This checks whether the result of a correlated nested query is empty (contains no tuples) or not
• The result of EXISTS is a boolean value True or False
• SELECT fname, Iname FROM Customers WHERE EXISTS (SELECT * FROM Orders WHERE Customers.customer_id = Orders.customer_id); will return something if exists

JOIN

• This combines rows from two or more tables, based on a related column between them
• Natural Join joins two tables based on same attribute name and datatypes
• The resulting table contains all the attributes of both the table but keep only one copy of each common column
• Inner Join returns records that have matching values in both tables based on explicit condition
• Left Outer Join returns all records from the left table, and the matched records from the right table
• Right Outer Join returns all records from the right table, and the matched records from the left table
• Full Outer Join returns all records when there is a match in either left or right table

Natural Join Example

• There is natural join on Student NATURAL JOIN Marks, which combines based on shared attributes

View

• Views in SQL are kind of virtual tables that store structure information
• A View can either have all the rows of a table or specific rows based on certain condition for security
• It can simplify a complex query
• CREATE VIEW Brazil_Customers AS SELECT CustomerName, ContactName FROM Customers WHERE Country = 'Brazil'; is an example
• You can select from a view with with: Select * from Brazil_Customers;
• Use Multiple table with: CREATE VIEW MarksView AS SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS FROM StudentDetails, StudentMarks WHERE StudentDetails.NAME = StudentMarks.NAME;
• CREATE OR REPLACE VIEW Brazil_Customers AS SELECT CustomerName, ContactName, City FROM Customers WHERE Country = "Brazil";
• You can drop a view with: Delete view Brazil_Customers;

Create or replace rules

• Rules for CREATE OR REPLACE statements include not changing certain things
• Rules to avoid including are: --Cannot change column name -- Cannot change column Data Type --Cannot change order of columns

select * from expensive_products; select * from tb_product_info; alter table tb_product_info add column prod_config varchar(100)

View Update Rules

• View queries should be formed using just 1 table/view

SQL/PSM

• PL/SQL: Procedural Language extensions to SQL in block structured

• DECLARE declaration statements; variable_name data_type := value // assignment

• BEGIN executable statements Variable:= &z; //input dbms_output.put_line(var); //output

• EXCEPTIONS exception handling statements

• END;

• Example SQL> SET SERVEROUTPUT ON; SQL> DECLARE - taking input for variable a a integer := &a ; -- taking input for variable b b integer := &b ; c integer; BEGIN c := a + b ; dbms_output.put_line('Sum of '||a||' and '||b||' is = '||c); END; /

Control Flow

• IF condition THEN statements END IF;
• IF condition-1 THEN statements; ELSIF condition-2 THEN statement; ELSIF condition-3 THEN statment; ELSE statements; END IF;
• Example DECLARE grade CHAR(1); BEGIN grade := 'B'; CASE grade WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END CASE; END; /;

Loops

• FOR index IN [REVERSE ] lower_bound..upper_bound LOOP statements END LOOP;
• A basic loop example

BEGIN
  FOR I IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE (i);
  END LOOP;
END;

Loop exmaples

• Example

DECLARE
a number(2);
BEGIN
FOR a in 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/

• A FOR loop in reverse

DECLARE
a number(2);
BEGIN
FOR a IN REVERSE 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/

• A nested for loop

DECLARE
i number(1);
j number(1);
BEGIN
FOR I IN 1..3 LOOP
FOR J IN 1..3 LOOP
dbms_output.put_line('i is: '|| i || ' and jis: ' || j);
END LOOP;
END LOOP;
END;
/

• A while loop example

DECLARE
i INTEGER := 1;
BEGIN
WHILE i <= 10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;

• Here is an EXIT example for loop

DECLARE
i NUMBER := 1;
BEGIN
LOOP
EXIT WHEN i>10;
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;

Loop breaking

• There are also break, and continue

Functions

• A function example structure shows how to show function name, parameter, the datatypes used and return types

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END;

• An adder function with parameters n1 and n2 to add together

create or replace function adder(n1 in number, n2 in number)
return number
is
n3 number(8);
begin
n3 :=n1+n2;
return n3;
end;
/

Example

DECLARE
n3 number(2);
BEGIN
n3 := adder(11,22);
dbms_output.put_line('Addition is: ' || n3);
END;
/

• Another example using select count to count customers

CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;
RETURN total;
END;

• Example

DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;

DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;

Procedures

• An example CREATE PROCEDURE statement CREATE [OR REPLACE] PROCEDURE name [(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS | AS} BEGIN < procedure_body > END;

CREATE PROCEDURE P_UPDATE_SALARY
BEGIN
UPDATE EMPLOYEES
SET SALARY = SALARY + (SALARY 0.10)
WHERE DEPARTMENT_ID=10;
DBMS_OUTPUT.PUT_LINE('SALARY UPDATED');

BEGIN
P_UPDATE_SALARY();
END;
/

CREATE PROCEDURE P_UPDATE_SALARY(P_DEPT_ID IN HUMBER, P_SAL_INCR NUMBER)
IS
BEGIN
UPDATE EMPLOYEES
SET SALARY = SALARY + (SALARY P_SAL_INCR)
WHERE DEPARTMENT_ID=P_DEPT_ID;
DBMS_OUTPUT.PUT_LINE('SALARY UPDATED FOR '11P_DEPT_ID);
END;

DECLARE
a number(5):=&a;
- -
get ID from user
b number(5):=&b; --get Increment value from user
BEGIN
P_UPDATE_SALARY(a, b);
END;
/

Cursor

• A Cursor is a temporary memory allocated by the database server performing data manipulation language - It holds the multiple rows returned by the SQL statement - implicit cursor - ecplicit cursor