Practice III: SQL (INSERT, UPDATE, DELETE, CREATE)

考慮以下的northwind_simple Database Model

• The Access database can be downloaded in the following link  northwind_simple.accdb   
• The Northwind database can also be tested online http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

請回答以下問題的SQL語法：

Q1. 依據以下資訊，新增一顧客

CustomerName: Providence University
ContactName: George Yeh
Address: 200, Sec. 7, Taiwan Boulevard
City: Taichung
PostalCode: 43301
Country: Taiwan

執行 SELECT語法確認新增成功。

執行 SELECT語法確認新增成功。

Q3. 將顧客名稱(CustomerName)為'Providence University' 之資料刪除。

執行 SELECT語法確認新增成功。

Q4. 依據一下資訊建立新表格Persons

(Hint: mySQL 請使用 AUTO_INCREMENT)

分別執行以下語法，確認是否都能成功?

INSERT INTO persons
(LastName, FirstName, Address, City)
VALUES
('Yeh', 'George', 'No. 1, Sec. 4, Roosevelt Rd.', 'Taipei');

INSERT INTO persons
(LastName, FirstName, Address)
VALUES
('Liu', 'HH', '200, Sec. 7, Taiwan Boulevard');

INSERT INTO persons
(P_id, LastName, FirstName, Address, City)
VALUES
(2, 'Wu', 'PC', '200, Sec. 7, Taiwan Boulevard', 'Taichung');

INSERT INTO persons
(P_id, LastName, FirstName, Address)
VALUES
(-200,'Wu', 'PC', '200, Sec. 7, Taiwan Boulevard');

Q5. 刪除Persons表格

執行語法確認刪除成功。

Q1: 
INSERT INTO Customers
(CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
('Providence University', 'George Yeh','200, Sec. 7, Taiwan Boulevard', 'Taichung', '43301','Taiwan');

Q2: 
UPDATE Customers
SET ContactName ='PC Wu'
WHERE CustomerName = 'Providence University';

Q2: 
DELETE FROM Customers
WHERE CustomerName = 'Providence University';

Q4: 
CREATE TABLE Persons
(
P_Id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255) DEFAULT 'Taichung', 
CHECK (P_Id>0)
);