By Fang Ying, Sham
Structured Query language (SQL)
SQL Commands
DCL (deals with access
TCL (deals with the DQL (retrieve data
DDL (define database DML (manipulate data rights and data control
transactions happening from the DB using SQL
schema in DBMS) present in the DB) on the data present in
in the DB) queries)
the db)
CREATE INSERT GRANT COMMIT SELECT
DROP UPDATE REVOKE ROLLBACK
ALTER DELETE
DDL : Data Definition Language DML: Data Manipulation Language
TRUNCATE DCL : Data Control Language TCL : Transaction Control Language
DQL : Data Query Language
1. Create database create database sample2
2. Use the database use sample2
3. Create table create table customer
(
customerid int identity(1,1) primary key,
customernumber int not null unique check (customernumber>0),
lastname varchar(30) not null,
firstname varchar(30) not null,
areacode int default 71000,
address varchar(50),
country varchar(50) default 'Malaysia'
)
4. Insert values into table insert into customer values
(100,'Fang Ying','Sham','418999','sdadasfdfd',default),
(200,'Mei Mei','Tan',default,'adssdsadsd','Thailand'),
(300,'Albert','John',default,'dfdsfsdf',default)
5. Display record from table -- display all records
select * from customer
-- display particular columns
select customerid, customernumber, lastname, firstname
from customer
6. Add new column to table alter table customer
add phonenumber varchar(20)
7. Add values to newly added update customer set phonenumber='1234545346' where
column/ Update table customerid=1
update customer set phonenumber='45554654' where
customerid=2
8. Delete a column alter table customer
drop column phonenumber
9. Delete record from table delete
--if not put ‘where’, will from customer
delete all record where country='Thailand'
10. Delete table drop table customer
11. Change data type alter table customer
alter column phonenumber varchar(10)
1
By Fang Ying, Sham
1. Create database create database SaleOrder
2. Use the database use SaleOrder
3. Create tables create table dbo.customer (
CustomerID int NOT null primary key,
CustomerFirstName varchar(50) NOT null,
CustomerLastName varchar(50) NOT null,
CustomerAddress varchar(50) NOT null,
CustomerSuburb varchar(50) null,
CustomerCity varchar(50) NOT null,
CustomerPostCode char(4) null,
CustomerPhoneNumber char(12) null,
);
create table dbo.inventory (
InventoryID tinyint NOT null primary key,
InventoryName varchar(50) NOT null,
InventoryDescription varchar(255) null,
);
create table dbo.employee (
EmployeeID tinyint NOT null primary key,
EmployeeFirstName varchar(50) NOT null,
EmployeeLastName varchar(50) NOT null,
EmployeeExtension char(4) null,
);
create table dbo.sale (
SaleID tinyint not null primary key,
CustomerID int not null references customer(CustomerID),
InventoryID tinyint not null references Inventory(InventoryID),
EmployeeID tinyint not null references Employee(EmployeeID),
SaleDate date not null,
SaleQuantity int not null,
SaleUnitPrice smallmoney not null
);
4. Check what table inside select * from information_schema.tables
5. View specific row --top: show only the first two
select top 2 * from customer
--top 40 percent: also means show the first two
select top 40 percent * from customer
6. View specific column --sort result (by default is ascending)
select customerfirstname, customerlastname from customer
order by customerlastname desc
select customerfirstname, customerlastname from customer
order by 4, 2, 3 desc -- Order By Based on column no. without typing column
name
--distinct: only show unique value
select distinct customerlastname from customer
order by customerlastname
