Assignment - 01

 

1. Assignment - 01
2. 
   
3. 
   
4. 
   
5. CREATE TABLE EMP (
6.     empid NUMBER PRIMARY KEY,
7.     name VARCHAR2(50),
8.     date_of_birth DATE,
9.     city VARCHAR2(50),
10.     salary NUMBER
11. );
12.  
13. -- Inserting Input 1
14. INSERT INTO EMP (empid, name, date_of_birth, city, salary)
15. VALUES (101, 'John Smith', TO_DATE('1990-05-15', 'YYYY-MM-DD'), 'New York', 60000);
16.  
17. -- Inserting Input 2
18. INSERT INTO EMP (empid, name, date_of_birth, city, salary)
19. VALUES (102, 'Alice Johnson', TO_DATE('1985-11-28', 'YYYY-MM-DD'), 'Los Angeles', 75000);
20.  
21. -- Inserting Input 3
22. INSERT INTO EMP (empid, name, date_of_birth, city, salary)
23. VALUES (103, 'Michael Davis', TO_DATE('1988-07-10', 'YYYY-MM-DD'), 'Chicago', 55000);
24.  
25.  
26. -- Create table
27. CREATE TABLE EMP_Constraint1 (
28.     id VARCHAR2(10) PRIMARY KEY,
29.     name VARCHAR2(50),
30.     city VARCHAR2(20) CHECK (city IN ('kolkata', 'delhi', 'mumbai')),
31.     sal NUMBER(5, 0) CHECK (sal >= 10000 AND sal <= 99999),
32.     comm NUMBER(4, 0) CHECK (comm >= 0 AND comm <= 9999),
33.     dob DATE,
34.     designation VARCHAR2(50) CHECK (designation = LOWER(designation)),
35.     dependence NUMBER DEFAULT 1,
36.     CONSTRAINT name_unique UNIQUE (name)
37. );
38.  
39. -- Insert data
40. INSERT ALL
41.     INTO EMP_Constraint (id, name, city, sal, comm, dob, designation, dependence)
42.     VALUES ('A102', 'John', 'kolkata', 15000, 500, TO_DATE('1999-05-20', 'YYYY-MM-DD'), 'clerk', 1)
43.    
44.     INTO EMP_Constraint (id, name, city, sal, comm, dob, designation, dependence)
45.     VALUES ('A103', 'Alice', 'mumbai', 28000, 200, TO_DATE('1995-09-12', 'YYYY-MM-DD'), 'manager', 2)
46.    
47.     INTO EMP_Constraint (id, name, city, sal, comm, dob, designation, dependence)
48.     VALUES ('A104', 'Bob', 'delhi', 21000, 0, TO_DATE('1998-03-05', 'YYYY-MM-DD'), 'analyst', 3)
49.    
50. SELECT 1 FROM DUAL;