Lab Database 10/7/2556

/* Create Database and use Database */
CREATE DATABASE Northwind
USE Northwind
/* Create Table */
CREATE TABLE Orders (
ord_id INT PRIMARY KEY,
emp_id VARCHAR(7),
cus_id VARCHAR(7),
shipName VARCHAR(30),
freight money,
qty INT,
orderDate datetime,
shipedDate datetime
);
 
/* Insert Data */
INSERT INTO Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) VALUES (10249, 'e0003', 'c0031' , 'North/South', 60.00, 24, '10/03/1997 00:00:00', '10/31/1997 00:00:00')
INSERT INTO Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) VALUES (10250, 'e0023', 'c0005' ,'The Cracker Box' ,155.33, 17 ,'10/13/1997 00:00:00' ,'11/24/1997 00:00:00')
INSERT INTO Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) VALUES (10251, 'e0021', 'c0157', 'Bon app', 108.00, 11, '01/15/1998 00:00:00', '02/12/1998 00:00:00')
INSERT INTO Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) VALUES (10252, 'e0011', 'c1019' ,'Around the Horn', 90.75, 15, '03/16/1998 00:00:00', '04/27/1998 00:00:00')
INSERT INTO Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) VALUES (10253, 'e0011', 'c1021', 'Eastern Connection', 215.21, 21, '04/09/1998 00:00:00', '05/07/1998 00:00:00')
INSERT INTO Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) VALUES (10254, 'e0017', 'c0733', 'The Cracker Box', 107.75, 14, '09/18/1996 00:00:00', '10/16/1996 00:00:00')
INSERT INTO Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) VALUES (10255, 'e0008', 'c0112', 'QUICK-Stop' ,135.33, 21, '08/08/1997 00:00:00', '09/05/1997 00:00:00')
INSERT INTO Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) VALUES (10256, 'e0021', 'c0107', 'Bon app', 231.15, 16, '11/28/1997 00:00:00', '12/26/1997 00:00:00')
INSERT INTO Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) VALUES (10257, 'e0003', 'c0052', 'Bon app', 132.25, 9, '03/04/1998 00:00:00', '04/01/1998 00:00:00')
INSERT INTO Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) VALUES (10258, 'e0005', 'c0050', 'QUICK-Stop', 178.67, 12, '11/27/1996 00:00:00', '12/25/1996 00:00:00')
INSERT INTO Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) VALUES (10259, 'e0017', 'c0015', 'Eastern Connection', 210.45 ,76, '04/15/1997 00:00:00', '05/13/1997 00:00:00')
/* Select Data to show */
SELECT * FROM Orders;

/*1*/
SELECT *,RIGHT(CAST(ord_id AS VARCHAR(10)),5) AS 'สินค้าของลูกค้าที่มี 5' FROM Orders WHERE ord_id LIKE '%5%'
/*2*/
SELECT shipName,REPLACE(shipName,'Around the Horn','Around the World') FROM Orders
/*3*/
SELECT shipedDate,datepart(DAY,(dateadd(DAY,5,shipedDate)))AS addday FROM Orders
/*4*/
SELECT * ,Len(shipName) AS 'ความยาวชื่อ', CEILING(freight) AS 'ปัดทศนิยม' FROM Orders WHERE freight>155
/*5*/
SELECT * FROM Orders WHERE ord_id>10252
/*6*/
SELECT * FROM Orders WHERE CAST(orderDate AS VARCHAR(30)) LIKE '%1998%'
/*7*/
SELECT *,Square(qty) AS 'ราคายกกำลัง 2' FROM Orders WHERE Square(qty) BETWEEN 120 AND 350
/*8*/
SELECT shipName,DateDiff(week,orderDate,shipedDate) AS 'จำนวนสัปดาห์' FROM Orders WHERE DateDiff(week,orderDate,shipedDate)>=4
/*9*/
SELECT SERVERPROPERTY('edition'),GetDate()