img
Question:
Published on: 23 December, 2024

Consider the following tables:

DEPT (DCODE, DNAME)

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

Write down the SQL statements for the following:

  1. For each department, show DNAME and total basic of the employee in the department.
  2. Find out the names of the department where no person is working.
  3. Find out the names of the employees who are working in the department named as ‘ABC’.
  4. 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
Software Engineering Compiler Design Database Management System Computer Graphics and Multimedia Data Structures & Algorithm Mobile computing
Trending

Consider the following relations and write down expressions for the following queries:

EMP (eid, ename, age, address, salary)

Works (eid, did, hours)

Dept (did, dname, managerid)

  1. List the names of employees who work for the ‘Research’ department for 8 hours using Relational Algebra.
  2. List the name and address of all employees with department number 5 using Relational Calculas.
  3. Find the managerid of managers who manage only departments with budgets greater than 1 lac using SQL.
  4. Find the employee who has highest salary using SQL.
View : 982
23 January, 2022

State the advantages of using database system over file-based information system.
View : 944
23 December, 2024

Discuss the ACID properties of transaction.
View : 1041
23 December, 2024

Let T1, T2, T3 be transactions that operate on the same data items A, B, C. Let r1(A) means that T1 reads A, w1(A) means that T1 writes A and so on for T2 and T3. Consider the following schedule: S1: r2(C), r2(B), w2(B), r3(C), r1(A), w1(A), w3(B), w3(C), r2(A), r1(B), w1(B), w2(A). Is the schedule serializable and why?
View : 836
23 December, 2024

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

  • Immediate updation
  • Deferred updation
View : 898
23 December, 2024
Random questions
About
MindStudy is a preparation hub for all the students. The goal is to make it easier for pupils to get better grades on exams. We have a list of questions with the best answer for each. Professors, Teachers, and Faculty members from a variety of universities, schools, and institutions throughout the world prepare the Q&A.
Contacts
Shunya Intelliware Solution
Kolkata, WB
Mail us at: info@mindstudy.in

Follow us on:
Streams
Top Subjects
Data Structures & Algorithm Database Management System Software Engineering Compiler Design Mobile computing Design and Analysis of Algorithm C Programming and Practices Computer Graphics and Multimedia Basic Electrical Engineering Radio Frequency and Microwave Engineering Analog Communication Electrical Engineering Management Concrete Technology Design of Reinforced Concrete Structures Power Electronics Irrigation Engineering Surveying Power System I
MindStudy © Copyright 2022, All rights reserved.
Home About Contact