Scribd
Upload
31 views4 pages

Assignment 1 DBMS

The document contains a series of SQL queries to retrieve and display data from database tables. The queries list employee details, display table columns and rows, show unique job names, concatenate employee names and jobs, replace null values, and calculate annual compensation in different ways by adding bonuses and commissions to monthly salaries.

Uploaded by

Aritra Saha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
31 views4 pages

Assignment 1 DBMS

The document contains a series of SQL queries to retrieve and display data from database tables. The queries list employee details, display table columns and rows, show unique job names, concatenate employee names and jobs, replace null values, and calculate annual compensation in different ways by adding bonuses and commissions to monthly salaries.

Uploaded by

Aritra Saha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

(a) List the names and employee number of all employees.

(b)Display all the columns and rows of dept table.

(c) Show all details of the emp table. Create a query to display the employee
number, name, job, hire date for each employee.
(d)Name the above column headings as Emp #, Employee, Job, and Hire Date,
respectively.

(e) Create a query to display unique job names from the emp table.
(f) Display the employee names concatenated with the jobs, separated by a comma
and space, and rename the column as Employee and Title.

(g)Create a query to display all the data from the emp table. Separate each column
by a comma. Replace null values with [Link] the column THE_OUTPUT.
select
concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(con
cat(concat(concat('',empno),','),ename),','),job),','),COALESCE(mgr,0)),','),hiredate),','),sal),','),
COALESCE(comm,0)),','),deptno) as The_Output from emp;

(h)Display name, salary, annual compensation of employees. It calculates the


annual compensation as monthly salary plus a monthly bonus of 100, multiplied by 12.
(i) Display name, salary, annual compensation of employees. It calculates the
annual compensation as 12 multiplied by monthly salary plus one time bonus of 100.

(j) Display name, salary, annual compensation of employees. It calculates the annual
compensation as monthly salary multiplied by 12 and then add commission to it.

You might also like