Advertisement
S_Madanska

06.10.2022

Jan 12th, 2022 (edited)
888
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 3.11 KB | None | 0 0
  1. CREATE DATABASE TRADECOMPANY
  2. USE TRADECOMPANY
  3.  
  4. ALTER DATABASE CURRENT COLLATE CYRILLIC_GENERAL_CI_AI
  5.  
  6. CREATE TABLE REGIONS
  7. (
  8.     REGION_ID SMALLINT NOT NULL PRIMARY KEY IDENTITY(1,1),
  9.     NAME VARCHAR(25) NOT NULL UNIQUE
  10. )
  11.  
  12. CREATE TABLE COUNTRIES
  13. (
  14.     COUNTRY_ID CHAR(2) NOT NULL,
  15.     NAME VARCHAR(40) NOT NULL,
  16.     REGION_ID SMALLINT NULL,
  17.  
  18.     CONSTRAINT PK_COUNTRIES PRIMARY KEY (COUNTRY_ID),
  19.     CONSTRAINT FK_COUNTRIES_REGIONS  FOREIGN KEY (REGION_ID)
  20.         REFERENCES REGIONS(REGION_ID)
  21.  
  22. )
  23.  
  24. CREATE TABLE CUSTOMERS
  25. (
  26.     CUSTOMER_ID NUMERIC(6) NOT NULL PRIMARY KEY,
  27.     COUNTRY_ID CHAR(2) NOT NULL,
  28.     FNAME VARCHAR(20) NOT NULL,
  29.     LNAME VARCHAR(20) NOT NULL,
  30.     ADDRESS TEXT  NULL,
  31.     EMAIL VARCHAR(30)  NULL,
  32.     GENDER CHAR(1) NULL DEFAULT 'M'
  33.         CHECK(GENDER IS NULL OR GENDER IN('M','F'))
  34. )
  35.  
  36. ALTER TABLE CUSTOMERS
  37. ADD CONSTRAINT FK_CUSTOMERS_COUNTRIES FOREIGN KEY (COUNTRY_ID)
  38.     REFERENCES COUNTRIES(COUNTRY_ID)
  39.  
  40. CREATE TABLE JOBS
  41. (
  42.     JOB_ID VARCHAR(10) NOT NULL PRIMARY KEY,
  43.     JOB_TITLE VARCHAR(35) NOT NULL,
  44.     MIN_SALARY NUMERIC(6) NULL,
  45.     MAX_SALARY NUMERIC(6) NULL
  46. )
  47.  
  48. CREATE TABLE EMPLOYEES
  49. (
  50.     EMPLOYEE_ID INT NOT NULL PRIMARY KEY,
  51.     FNAME VARCHAR(20) NOT NULL,
  52.     LNAME VARCHAR(25) NOT NULL,
  53.     EMAIL VARCHAR(40) NOT NULL UNIQUE,
  54.     PHONE VARCHAR(20) NULL,
  55.     HIRE_DATE DATETIME NOT NULL,
  56.     SALARY NUMERIC(8,2) NOT NULL CHECK(SALARY>0),
  57.     JOB_ID VARCHAR(10) NOT NULL,
  58.     MANAGER_ID INT NULL,
  59.     DEPARTMENT_ID INT NULL,
  60.  
  61.     CONSTRAINT FK_EMP_JOB FOREIGN KEY (JOB_ID)
  62.         REFERENCES JOBS(JOB_ID),
  63.     CONSTRAINT FK_EMP_MNG FOREIGN KEY (MANAGER_ID)
  64.         REFERENCES EMPLOYEES(EMPLOYEE_ID)
  65. )
  66.  
  67. CREATE TABLE DEPARTMENTS
  68. (
  69.     DEPARTMENT_ID INT NOT NULL PRIMARY KEY,
  70.     NAME VARCHAR(30) NOT NULL,
  71.     MANAGER_ID INT NULL,
  72.     COUNTRY_ID CHAR(2) NOT NULL,
  73.     CITY VARCHAR(30) NOT NULL,
  74.     STATE VARCHAR(25) NULL,
  75.     ADDRESS VARCHAR(40) NULL,
  76.     POSTAL_CODE VARCHAR(12) NULL,
  77.  
  78.     CONSTRAINT FK_DEPT_COUNTRY FOREIGN KEY (COUNTRY_ID)
  79.         REFERENCES COUNTRIES(COUNTRY_ID),
  80.     CONSTRAINT FK_DEPT_MNG FOREIGN KEY (MANAGER_ID)
  81.         REFERENCES EMPLOYEES(EMPLOYEE_ID)
  82. )
  83.  
  84. ALTER TABLE EMPLOYEES
  85. ADD CONSTRAINT FK_EMP_DEPT FOREIGN KEY (DEPARTMENT_ID)
  86.     REFERENCES DEPARTMENTS(DEPARTMENT_ID)
  87.  
  88. CREATE TABLE PRODUCTS
  89. (
  90.     PRODUCT_ID INT NOT NULL PRIMARY KEY,
  91.     NAME VARCHAR(70) NOT NULL,
  92.     PRICE NUMERIC(8,2) NOT NULL,
  93.     DESCR VARCHAR(2000) NULL
  94. )
  95.  
  96. CREATE TABLE ORDERS
  97. (
  98.     ORDER_ID INT NOT NULL PRIMARY KEY,
  99.     ORDER_DATE DATETIME NOT NULL,
  100.     CUSTOMER_ID NUMERIC(6) NOT NULL FOREIGN KEY REFERENCES CUSTOMERS,
  101.     EMPLOYEE_ID INT NOT NULL FOREIGN KEY REFERENCES EMPLOYEES,
  102.     SHIP_ADDRESS VARCHAR(150) NULL
  103.  
  104.     --CONSTRAINT FK_O_CUST FOREIGN KEY (CUSTOMER_ID)
  105.     --REFERENCES CUSTOMERS(CUSTOMER_ID),
  106.     --CONSTRAINT FK_O_EMP FOREIGN KEY (EMPLOYEE_ID)
  107.     --REFERENCES EMPLOYEES  (EMPLOYEE_ID)
  108. )
  109.  
  110. CREATE TABLE ORDER_ITEMS
  111. (
  112.     ORDER_ID INT NOT NULL,
  113.     PRODUCT_ID INTEGER NOT NULL,
  114.         PRIMARY KEY(ORDER_ID , PRODUCT_ID),
  115.     UNIT_PRICE NUMERIC(8,2) NOT NULL,
  116.     QUANTITY NUMERIC(8) NOT NULL,
  117.  
  118.     CONSTRAINT FK_OI_PRODUCTS FOREIGN KEY (PRODUCT_ID)
  119.         REFERENCES PRODUCTS(PRODUCT_ID),
  120.     CONSTRAINT FK_OI_ORDERS FOREIGN KEY (ORDER_ID)
  121.         REFERENCES ORDERS (ORDER_ID)
  122.             ON UPDATE CASCADE ON DELETE CASCADE
  123. )
  124.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement