SQL Notes

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


2

, By Fang Ying, Sham


7. Save table to another table --into file_name: save result in another table (BASE TABLE)
select distinct customerlastname into temp
from customer
order by customerlastname

select * from temp --see the table (data type will remain)

8. Like (search something) -- (underscore sign) _ is only specific for one character only
-- (percent sign) % represents zero, one, or multiple characters
select * from customer
where customerlastname like '_r%'

9. In (search something) -- search multiple items
select * from customer
where customerlastname in ('Brown', ‘Michael’, ’Jim’)

10. > (search something) select * from customer
where customerlastname > 'Brown' or customerlastname>'Cross'
11. <> (Not Equal) select * from customer
where customerlastname <> 'Brown'

12. IS NULL -- check null values
select * from customer
where customerlastname IS NULL

13. IS NOT NULL select * from customer
where customerlastname IS NOT NULL

14. between select * from sale
where saleunitprice between 5 and 10 --not include 5 & 10

15. count -- returns the number of rows in a table
-- AS means aliasing, temporary giving name to a column/ table
select count(*) as [Number of Records] from customer
where customerfirstname like 'B%'

16. sum select sale.employeeid ,EmployeeFirstName, EmployeeLastName , count(*) as
[Number of order] ,
sum(salequantity) as [Total Quantity]
from sale,employee
where sale.employeeid = employee.employeeid
group by sale.employeeid ,EmployeeFirstName, EmployeeLastName

17. count month select month(saledate) as [Month], count ( * ) as [Number of sale],
sum(salequantity*saleunitprice) as [Total Amount]
from sale
group by month(saledate)

18. max SELECT MAX(Salary)
FROM EmployeeSalary
19. min SELECT MIN(Salary)
FROM EmployeeSalary
20. average SELECT AVG(Salary)
FROM EmployeeSalary


3