Tiêu đề Tai-lieu-SQL-t3h-share.pdf ✅

By Fang Ying, Sham 1 Structured Query language (SQL) 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 column/ Update table update customer set phonenumber='1234545346' where 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 --if not put ‘where’, will delete all record delete from customer where country='Thailand' 10. Delete table drop table customer 11. Change data type alter table customer alter column phonenumber varchar(10) SQL Commands DDL (define database schema in DBMS) CREATE DROP ALTER TRUNCATE DML (manipulate data present in the DB) INSERT UPDATE DELETE DCL (deals with access rights and data control on the data present in the db) GRANT REVOKE TCL (deals with the transactions happening in the DB) COMMIT ROLLBACK DQL (retrieve data from the DB using SQL queries) SELECT DDL : Data Definition Language DML: Data Manipulation Language DCL : Data Control Language TCL : Transaction Control Language DQL : Data Query Language
By Fang Ying, Sham 2 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
By Fang Ying, Sham 3 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
By Fang Ying, Sham 4 21. having SELECT JobTitle, COUNT(JobTitle) FROM EmployeeDemographics ED JOIN EmployeeSalary ES ON ED.EmployeeID = ES.EmployeeID GROUP BY JobTitle HAVING COUNT(JobTitle) > 1 SELECT JobTitle, AVG(Salary) FROM EmployeeDemographics ED JOIN EmployeeSalary ES ON ED.EmployeeID = ES.EmployeeID GROUP BY JobTitle HAVING AVG(Salary) > 45000 ORDER BY AVG(Salary) 22. Change data type temporary for use -- CAST(expression AS datatype(length)) SELECT CAST('2017-08-25 00:00:00.000' AS date) -- CONVERT(data_type(length), expression, style) SELECT CONVERT(date,'2017-08-25 00:00:00.000') 23. CASE Statement SELECT FirstName, LastName, Age, CASE WHEN Age > 30 THEN 'Old' WHEN Age BETWEEN 27 AND 30 THEN 'Young' ELSE 'Baby' END FROM EmployeeDemographics ED WHERE Age IS NOT NULL ORDER BY Age -- SELECT FirstName, LastName, JobTitle, Salary, CASE WHEN JobTitle = 'Salesman' THEN Salary + (Salary *.10) WHEN JobTitle = 'Accountant' THEN Salary + (Salary *.05) WHEN JobTitle = 'HR' THEN Salary + (Salary *.000001) ELSE Salary + (Salary *.03) END AS SalaryAfterRaise FROM EmployeeDemographics ED JOIN EmployeeSalary ES ON ED.EmployeeID = ES.EmployeeID 24. Partition By --returns a single value for each row SELECT FirstName, LastName, Gender, Salary, COUNT(Gender) OVER (PARTITION BY Gender) AS TotalGender FROM EmployeeDemographics ED JOIN EmployeeSalary ES ON ED.EmployeeID = ES.EmployeeID