MindStudy - MindStudy

Question:

Published on: 23 February, 2025

Consider the following tables:

DEPT (DCODE, DNAME)

EMP (ECODE, ENAME, BASIC, DCODE, DT_JN)

Write down the SQL statements for the following:

For each department, show DNAME and total basic of the employee in the department.
Find out the names of the department where no person is working.
Find out the names of the employees who are working in the department named as ‘ABC’.
Find out the maximum basic among the employees who have joined after year 2000.

Answer:

a. SELECT DNAME, SUM(BASIC) “TOTAL” FROM DEPT, EMP WHERE DEPT.DCODE=EMP.DCODE GROUP BY DNAME;

b. SELECT DNAME FROM DEPT WHERE NOT EXISTS (SELECT * FROM EMP WHERE DEPT.DCODE=EMP.DCODE);

c. SELECT ENAME FROM EMP, DEPT WHERE EMP.DCODE=DEPT.DCODE AND DNAME=’ABC’;

d. SELECT MAX(BASIC) “MAX_BASIC” FROM EMP WHERE DT_JN>01JAN2000;

Subjects

Mobile computing Database Management System C Programming and Practices Data Structures & Algorithm Design and Analysis of Algorithm Compiler Design

Trending

What is attribute inheritance? Describe the concept of specialization and generalization in the context of ER-data model.

23 February, 2025

With suitable examples, show how recovery in a database system can be done using Log Files with

23 February, 2025

Define BCNF. How does it differ from 3NF? Why is it considered stronger than 3NF?

23 February, 2025

Let R (ABCDE) be a relation schema and consider the following functional dependencies F = { AB→ E, AD→B, B→ C, C →D}. Find out the candidate key.

23 February, 2025

What do you mean by ‘Ternary Relationship’? Define the concept of aggregation with suitable example.

23 February, 2025

Perspective Projection