SQL COMMANDS

SQL Languages:

● DDL: Data Definition Language

○ The DDL provides a set of definitions to specify the storage structure
and access methods used by the database system.
● DML: Data Manipulation Language
○ Data Manipulation Language(DML) is a language that enables users to
access or manipulate data as organized by the appropriate data model.
● DCL: Data Control Language
○ It is used to control access to the database (by GRANTing / REVOKing
etc.) and therefore essential to the system.

SQL DATA TYPES:

int

number

varchar2

decimal

date

char

CREATE CREATE TABLE employee

(
ecode integer
ename varchar2(20)
sex varchar2(1)
grade char (2),
gross decimal ) ;

create table MOVIE

(Movie_no integer,
Title varchar2(15),
Type varchar2(10),
Star varchar2(25),
Price number(8,2))

To show ● desc tablename; (oracle only)

structure ● sp_help tablename (sql server)

ALTER Alter tablel is to alter structure of table

ALTER TABLE <table name>
ADD <column name> <data type><size>;

ALTER TABLE Emp

ADD (tel number ) ;
ALTER TABLE bts
DROP COLUMN id ;

alter table CUST

add AGE integer;
Alter table name:
ALTER TABLE bts
rename column name to bname

eg) create table branch

(
bid integer,
name varchar2(10),
add varchar2(20)
)
desc branch;

alter table branch

add (telno number(8))

UPDATE: Update table is to Update data of table

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

Insert Insert all values:

insert into CUST values('A01','Border','Allan','SA',723622)
Insert specific values:
insert into
CUST(Cust_id,Fname,Area,Phone_no)values('A05','Sachin','Dr',25348
9)
Insert into table 1 from table 2:
INSERT INTO table1
SELECT * FROM table2
WHERE pay>500;
Delete: delete from INVOICE
where Return_date < '10-jul-95';

Truncate:
truncate table MOVIE;

SELECT Select all:

select*from CUSTOMER;
Select Specific Attributes from table branch:
SELECT name, bid
FROM branch;

Print w a specific sentence:

create table INVOICE
( Inv_no varchar2(3),
Movie_no integer,
Cust_id varchar2(3),
Issue_date Date,
Return_date Date );

select 'The Invoice No. Of Customer Id.' + Cust_id + ' is ' + Inv_no +'
and Movie No. Is ' + cast(Movie_no as varchar) from INVOICE;

We can specify only CHAR, NCHAR, VARCHAR or NVARCHAR data

types in the PRINT statement. In this case, it implicitly converts an
integer value to the VARCHAR data type internally.

DISTINCT SELECT distinct names

: FROM Movie;

Select count(distinct names)

From movie;

AVG, Select MAX (Age) from STUDENT where Sex = "F" ;

SUM,COU Select AVG (Fee) from STUDENT where Dateofadm <(01/0188);
NT,MIN,M Select SUM (Fee) from STUDENT where Dateofadm <101/0188);
AX,STDD SELECT COUNT(*) FROM Books;
EV,VARIA
NCE:

WHERE: select Type, AVG(Price)

from MOVIE
where Type like 'Comedy' or Type like 'Thriller' ;

Group The GROUP BY statement is often used with aggregate functions

by: (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one
or more columns.
 select Type,AVG(Price)
from MOVIE
where Type like 'Comedy' or Type like 'Thriller'
group by Type having AVG(Price);

HAVING:

ALIAS

SQL aliases are used to give a table, or a column in a table, a

temporary name. Aliases are often used to make column names more
readable. An alias only exists for the duration of that query.

LIVESQL

SQL SERVER:
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', '
+ Country AS Address
FROM Customers;

select Fname,Title ,Type from MOVIE m,INVOICE i,CUST c where

Fname like 'Tina' and m.Movie_no = i.Movie_no and c.Cust_id =
i.Cust_id;

UNION
Combines results of two or more queries into. Columns must have the
same data type and there must be the same number of columns
chosen. Columns must be in the same order.
 EXISTS
The EXISTS operator returns TRUE if the subquery returns one or
more records.
 VIEWS

A View is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a
view are fields from one or more real tables in the database.

Why do we need a view?

1. Views can hide the complexity:

a. If you have a query that requires joining several tables,
or has complex logic or calculations, you can code all
that logic into a view, then select from the view just like
you would a table.
2. Views can be used as a security mechanism
a. A view can select certain columns and/or rows from a
table (or tables), and permissions are set on the view
instead of the underlying tables. This allows surfacing
only the data that a user needs to see.
3. Views are also very useful when refactoring databases. You can
often hide the change so that the old code doesn't see it by
creating a view.

1) Create view:
create view HELLO as
select c.Cust_id,Fname, Title
from INVOICE i, CUST c, MOVIE m
where (Fname like 'Tina' or Fname like 'Allan') and i.Movie_no=
m.Movie_no and i.Cust_id= c.Cust_id;
select * from HELLO;

2) Alter view:
alter view HELLO as
select c.Cust_id,Fname, Title, Price
from INVOICE i, CUST c, MOVIE m
where (Fname like 'Tina' or Fname like 'Allan') and i.Movie_no=
m.Movie_no and i.Cust_id= c.Cust_id;
select * from HELLO;

3) Drop view: drop view HELLO;

 Constraints
Constraints are used to limit the type of data that can go into a table.
This ensures the accuracy and reliability of the data in the table. If
there is any violation between the constraint and the data action, the
action is aborted. Constraints can be column level or table level.

● NOT NULL - Ensures that a column cannot have a NULL value

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255) NOT NULL,

Age int

);

● UNIQUE - Ensures that all values in a column are different

SQL Server / Oracle / MS Access:

CREATE TABLE Persons (

ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
 My SQL:

CREATE TABLE Persons (

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

Unique constraint for multiple cols:

Constraint UC_Persons UNIQUE( column1,column2);
Drop Constraint:
ALTER TABLE Persons
DROP CONSTRAINT UC_Person;

● PRIMARY KEY - A combination of a NOT NULL and UNIQUE.

Uniquely identifies each row in a table

CONSTRAINT PK_Person PRIMARY KEY (ID, LastName)

● FOREIGN KEY - Prevents actions that would destroy links

between tables

MySQL:

CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES
Persons(PersonID)
);
SQL SERVER/ORACLE:
CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES
Persons(PersonID)
);

● DEFAULT - Sets a default value for a column if no value is

specified
● CHECK:The CHECK constraint is used to limit the value range
that can be placed in a column.

CREATE TABLE Persons (

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

AUTO Sql server:

inc: CREATE TABLE Persons (
Personid int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
Mysql etc:
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);
ALTER TABLE Persons AUTO_INCREMENT=100;

ALTER UPDATE

1. ALTER command is Data 1. UPDATE Command is a Data

Definition Language (DDL). Manipulation Language (DML).
2. Alter command will perform the 2. The update command will
action on the structure level and perform on the data level
not on the data level. 3. UPDATE Command is used to
3. ALTER Command is used to add, update existing records in a
delete, modify the attributes of database.
the relations (tables) in the
database.

DELETE DROP

1. Data Manipulation Language 1. Data Definition Language

command command
2. It removes some or all the tuples 2. It removes the entire schema,
from a table. table, domain, or constraints from
3. WHERE clause is mainly used the database.
along with the DELETE 3. No clause is required along with
command. the DROP command.

DROP TRUNCATE
 1. It removes the entire schema, 1. The TRUNCATE command is
table, domain, or constraints from used to delete all the rows from
the database. the table.
2. In the DROP command, table 2. the TRUNCATE command does
space is freed from memory. not free the tablespace from
3. In the DROP command, a view of memory.
the table does not exist. 3. While in this command, a view of
the table exists.

DELETE TRUNCATE

1. Data Manipulation Language 1. DDL language

command 2. The TRUNCATE command is
2. It removes some or all the tuples used to delete all the rows from
from a table. the table.
3. DELETE command can filter the 3. The TRUNCATE command does
record/tuples by using the not allow to use WHERE clause,
WHERE clause. so we cannot filter rows while
truncating.

Next triggers,censors,functions.