SHARE
TWEET

SQL Database #1

george1119 Oct 12th, 2019 177 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE DATABASE PCTrade_2a
  2.  
  3. CREATE TABLE REGIONS
  4. (
  5.     REGION_ID TINYINT PRIMARY KEY,
  6.     NAME VARCHAR(25) not null,
  7.  
  8. )
  9.  
  10. CREATE TABLE JOBS
  11. (
  12.     JOB_ID VARCHAR(10) PRIMARY KEY,
  13.     JOB_TITLE VARCHAR(35) NOT NULL,
  14.     MIN_SALARY NUMERIC(6),
  15.     MAX_SALARY NUMERIC(6),
  16. )
  17.  
  18. CREATE TABLE PRODUCTS
  19. (
  20.     PRODUCT_ID INT PRIMARY KEY,
  21.     NAME VARCHAR(50) NOT NULL,
  22.     PRICE NUMERIC(8,2) NOT NULL,
  23.     DESCR VARCHAR(2000)
  24. )
  25.  
  26. CREATE TABLE COUNTRIES
  27. (
  28.     COUNTRY_ID CHAR(2) PRIMARY KEY,
  29.     NAME VARCHAR(40) NOT NULL,
  30.     REGION_ID TINYINT FOREIGN KEY REFERENCES REGIONS ON DELETE SET NULL,
  31.  
  32. )
  33.  
  34. CREATE TABLE CUSTOMERS
  35. (
  36.     CUSTOMER_ID NUMERIC(6) PRIMARY KEY,
  37.     FNAME VARCHAR(20) NOT NULL,
  38.     LNAME VARCHAR(20) NOT NULL,
  39.     COUNTRY_ID CHAR(2) NOT NULL FOREIGN KEY REFERENCES COUNTRIES,
  40.     ADDRESS TEXT,
  41.     EMAIL VARCHAR(30),
  42.     GENDER CHAR(1) CHECK (GENDER IS NULL OR GENDER IN('M', 'F'))
  43. )
  44.  
  45. CREATE TABLE DEPARTMENTS
  46. (
  47.     DEPARTMENT_ID INT PRIMARY KEY,
  48.     NAME VARCHAR(30) NOT NULL,
  49.     COUNTRY_ID CHAR(2) NOT NULL FOREIGN KEY REFERENCES COUNTRIES,
  50.     MANAGER_ID INT,
  51.     CITY VARCHAR(30) NOT NULL,
  52.     STATE VARCHAR(25),
  53.     ADDRESS VARCHAR(40),
  54.     POSTAL_CODE VARCHAR(12)
  55. )
  56.  
  57. CREATE TABLE EMPLOYEES
  58. (
  59.     EMPLOYEE_ID INT PRIMARY KEY,
  60.     FNAME VARCHAR(20) NOT NULL,
  61.     LNAME VARCHAR(20) NOT NULL,
  62.     EMAIL VARCHAR(20) NOT NULL UNIQUE,
  63.     PHONE VARCHAR(20),
  64.     HIRE_DATE DATETIME NOT NULL,
  65.     SALARY NUMERIC(8,2) NOT NULL CHECK (SALARY > 0),
  66.     JOB_ID VARCHAR(10) NOT NULL FOREIGN KEY REFERENCES JOBS,
  67.     MANAGER_ID INT FOREIGN KEY REFERENCES EMPLOYEES,
  68.     DEPARTMENT_ID INT FOREIGN KEY REFERENCES DEPARTMENTS
  69. )
  70.  
  71. ALTER TABLE DEPARTMENTS
  72. ADD FOREIGN KEY(MANAGER_ID) REFERENCES EMPLOYEES
  73.  
  74. CREATE TABLE ORDERS
  75. (
  76.     ORDER_ID INT PRIMARY KEY,
  77.     ORDER_DATE DATETIME NOT NULL,
  78.     CUSTOMER_ID NUMERIC(6) NOT NULL FOREIGN KEY REFERENCES CUSTOMERS,
  79.     EMPLOYEE_ID INT NOT NULL FOREIGN KEY REFERENCES EMPLOYEES,
  80.     SHIP_ADDRESS VARCHAR(150)
  81. )
  82.  
  83. CREATE TABLE ORDER_ITEMS
  84. (
  85.     ORDER_ID INT NOT NULL FOREIGN KEY REFERENCES ORDERS ON DELETE CASCADE,
  86.     PRODUCT_ID INT NOT NULL FOREIGN KEY REFERENCES PRODUCTS,
  87.     PRIMARY KEY(ORDER_ID, PRODUCT_ID),
  88.     UNIT_PRICE NUMERIC(8,2) NOT NULL,
  89.     QUANTITY NUMERIC(8) NOT NULL
  90.  
  91. )
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top