יצירת טבלאות SQL – ניתן להעזר בדוגמאות קוד אלו כדי לתרגל את יצירת הטבלאות, בנוסף הייתי ממליץ להעזר בפוסט הבא
-- DROP DATABASE testDB CREATE DATABASE testDB GO USE testDB GO -------------------------- -- DDL -------------------------- ------------------------- -- CREATE TABLE EX1 ------------------------- CREATE TABLE employees (employeeID int, lastName varchar(25)) DROP TABLE employees ------------------------- -- CREATE TABLE EX2 (IDENTITY / DEFAULT) ------------------------- CREATE TABLE employees (employeeID int IDENTITY(1,1) , lastName varchar(25), salary money DEFAULT 4000 ) INSERT INTO employees VALUES (1, 'yossi' , 6000) INSERT INTO employees VALUES ( 'yossi' , 6000) SELECT * FROM employees -- Implicit Insert INSERT INTO employees VALUES ('moshe' , DEFAULT) -- Explicit Insert INSERT INTO employees (lastName) VALUES ('haim') SELECT * FROM employees DROP TABLE employees ------------------------- -- CREATE TABLE EX3 (CONSTRAINTS) ------------------------- -- NOT NULL DROP TABLE employees CREATE TABLE employees (employeeID int , lastName varchar(25) NOT NULL , salary money DEFAULT 4000 ) INSERT INTO employees VALUES (1, NULL , 6000) -- UNIQUE DROP TABLE employees CREATE TABLE employees (employeeID int , lastName varchar(25) NOT NULL , salary money DEFAULT 4000 , phoneNumber varchar(25) UNIQUE) INSERT INTO employees VALUES (1, 'Moshe' , 6000 , '03-5555555') INSERT INTO employees VALUES (2, 'Yossi' , 7000 , '03-5555555') -- PRIMARY KEY DROP TABLE employees CREATE TABLE employees (employeeID int PRIMARY KEY , lastName varchar(25) NOT NULL , salary money DEFAULT 4000 , phoneNumber varchar(25) UNIQUE) INSERT INTO employees VALUES (1, 'Moshe' , 6000 , '03-5555555') INSERT INTO employees VALUES (1, 'Yossi' , 7000 , '03-7777777') INSERT INTO employees VALUES (NULL, 'Yossi' , 7000 , '03-7777777') -- CHECK 1 DROP TABLE employees CREATE TABLE employees (employeeID int PRIMARY KEY , lastName varchar(25) NOT NULL , salary money DEFAULT 4000 CHECK (salary BETWEEN 0 AND 10000), phoneNumber varchar(25) UNIQUE) INSERT INTO employees VALUES (1, 'Moshe' , 10001 , '03-5555555') INSERT INTO employees VALUES (1, 'Moshe' , 9999 , '03-5555555') -- CHECK 2 DROP TABLE employees CREATE TABLE employees (employeeID int PRIMARY KEY , lastName varchar(25) NOT NULL , salary money DEFAULT 4000 CHECK (salary BETWEEN 0 AND 10000), phoneNumber varchar(25) UNIQUE , email varchar(25) CHECK (email LIKE '%@gmail.com')) INSERT INTO employees VALUES (1, 'Moshe' , 9999 , '03-5555555' , 'moshe@walla.co.il') INSERT INTO employees VALUES (1, 'Moshe' , 9999 , '03-5555555' , 'moshe@gmail.com') -- FOREIGN KEY DROP TABLE employees CREATE TABLE departments (department_id int PRIMARY KEY, department_name varchar(25)) INSERT INTO departments VALUES (50 , 'IT') INSERT INTO departments VALUES (60 , 'HR') INSERT INTO departments VALUES (70 , 'SA') CREATE TABLE employees (employeeID int PRIMARY KEY , lastName varchar(25) NOT NULL , salary money DEFAULT 4000 CHECK (salary BETWEEN 0 AND 10000), phoneNumber varchar(25) UNIQUE , email varchar(25) CHECK (email LIKE '%@gmail.com'), department_id int REFERENCES departments (department_id)) INSERT INTO employees VALUES (1, 'Moshe' , 9999 , '03-5555555' , 'moshe@gmail.com' , 50 ) INSERT INTO employees VALUES (2, 'Yossi' , 9999 , '03-7777777' , 'yossi@gmail.com' , 999 ) DELETE FROM departments WHERE department_id = 50 DELETE FROM departments WHERE department_id = 60