[go: up one dir, main page]
Scribd
Upload
4 views4 pages

DBMS Assesment

The document outlines the creation of two tables, 'depts' and 'emps', in a database, including their structure and initial data insertion. It also specifies constraints for both tables, such as primary keys, unique constraints, and foreign keys. Additionally, it includes SQL queries for data retrieval based on specific conditions.

Uploaded by

2k22.csaiml.2211026
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
4 views4 pages

DBMS Assesment

The document outlines the creation of two tables, 'depts' and 'emps', in a database, including their structure and initial data insertion. It also specifies constraints for both tables, such as primary keys, unique constraints, and foreign keys. Additionally, it includes SQL queries for data retrieval based on specific conditions.

Uploaded by

2k22.csaiml.2211026
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

NAME- SUCHARITA PAL , ROLL NO- 2201641530178

…………………………………………………………………………………………………………………………………………………………..

1. CREATE TABLE depts (

dept_code NUMBER,

dept_title VARCHAR2(10)

);

CREATE TABLE emps (

emp_code NUMBER,

emp_fname VARCHAR2(50),

dept_code NUMBER,

manager_id NUMBER,

join_date DATE,

salary NUMBER(10, 2)

);

Insert data into depts table

INSERT INTO depts (dept_code, dept_title) VALUES (1, 'HR');

INSERT INTO depts (dept_code, dept_title) VALUES (2, 'IT');

INSERT INTO depts (dept_code, dept_title) VALUES (3, 'Finance');

INSERT INTO depts (dept_code, dept_title) VALUES (4, 'Marketing');

Insert data into emps table

INSERT INTO emps (emp_code, emp_fname, dept_code, manager_id, join_date, salary)

VALUES (1, 'Robert', 1, NULL, TO_DATE('01-Jan-2022', 'DD-MON-YYYY'), 60000.00);

INSERT INTO emps (emp_code, emp_fname, dept_code, manager_id, join_date, salary)

VALUES (2, 'Silvia', 2, 1, TO_DATE('15-Feb-2020', 'DD-MON-YYYY'), 80000.00);

INSERT INTO emps (emp_code, emp_fname, dept_code, manager_id, join_date, salary)

VALUES (3, 'Amar', 2, 1, TO_DATE('10-Jan-2022', 'DD-MON-YYYY'), 80000.00);


INSERT INTO emps (emp_code, emp_fname, dept_code, manager_id, join_date, salary)

VALUES (4, 'Akbar', 3, 2, TO_DATE('20-Apr-2022', 'DD-MON-YYYY'), 30000.00);

INSERT INTO emps (emp_code, emp_fname, dept_code, manager_id, join_date, salary)

VALUES (5, 'Anthony', 3, 2, TO_DATE('05-May-2022', 'DD-MON-YYYY'), 60000.00);

INSERT INTO emps (emp_code, emp_fname, dept_code, manager_id, join_date, salary)

VALUES (6, 'David', 4, 3, TO_DATE('15-Jun-2022', 'DD-MON-YYYY'), 80000.00);

INSERT INTO emps (emp_code, emp_fname, dept_code, manager_id, join_date, salary)

VALUES (7, 'Grace', 4, 3, TO_DATE('01-Jul-2022', 'DD-MON-YYYY'), 80000.00);

INSERT INTO emps (emp_code, emp_fname, dept_code, manager_id, join_date, salary)

VALUES (8, 'Frank', 1, NULL, TO_DATE('10-Aug-2022', 'DD-MON-YYYY'), 65000.00);

INSERT INTO emps (emp_code, emp_fname, dept_code, manager_id, join_date, salary)

VALUES (9, 'Saksham', 1, NULL, TO_DATE('02-Oct-2024', 'DD-MON-YYYY'), 80000.00);

INSERT INTO emps (emp_code, emp_fname, dept_code, manager_id, join_date, salary)

VALUES (10, 'Vidushi', 1, 1, TO_DATE('12-Jan-2014', 'DD-MON-YYYY'), 60000.00);

2. APPLY CONSTRAINTS

=> On dept table

ALTER TABLE depts

ADD CONSTRAINT pk_depts PRIMARY KEY (dept_code);

ALTER TABLE depts

MODIFY dept_title VARCHAR2(10) NOT NULL;

=>On emps table


ALTER TABLE emps

ADD CONSTRAINT pk_emps PRIMARY KEY (emp_code);

ALTER TABLE emps

MODIFY emp_fname VARCHAR2(50) NOT NULL;

ALTER TABLE emps

ADD CONSTRAINT uq_emp_fname UNIQUE (emp_fname);

ALTER TABLE emps

ADD CONSTRAINT fk_emps_dept_code FOREIGN KEY (dept_code)

REFERENCES depts (dept_code);

ALTER TABLE emps

ADD CONSTRAINT fk_emps_manager_id FOREIGN KEY (manager_id)

REFERENCES emps (emp_code);

ALTER TABLE emps

MODIFY join_date DATE NOT NULL;

3.

SELECT emp_code, emp_fname, dept_code, manager_id, join_date, salary

FROM emps

WHERE TO_CHAR(join_date, 'MON') = 'APR';

4.

SELECT dept_code, MIN(salary) AS min_salary

FROM emps

GROUP BY dept_code

ORDER BY dept_code;

5.

SELECT emp_code, emp_fname, dept_code, manager_id, join_date, salary


FROM emps

WHERE manager_id = (

SELECT emp_code

FROM emps

WHERE emp_fname = 'Silvia'

);

You might also like