SQL Assignment 1

we are  performing SQL operation for that used following table as :

Table Employee
Figure 1.0

 1) Display the following virtual columns.

Exam Fee 25% Discount Exam Fee After Discount 18% GST Final Exam Fees
9701        

If the Exam fee is Rs.9701 calculate the rest.

Ans : select 9701 as “Exam Fee”, (9701*(25/100)) as “25% Discount” , 9701-(9701*0.25) as “Exam Fee After Discount” , (9701-(9701*0.25))*0.18 as “18% GST”, ((9701-(9701*0.25))+((9701-(9701*0.25))*0.18)) as “Final Exam Fee” from dual;

2) Display the below sentence for the employees.  SMITH’S salary is 800 and his dream salary is 3 times i.e. = ________.

Ans: select ename||q'<‘s>’||’ salary is ‘|| sal ||’ and his dream salary is 3 times =’ || sal*3 from emp;

3) Calculate annual salary for all employees with a monthly deduction of 20%. Label the column as “Annual Salary after 20% Deduction”.

Ans: select ename as “Name”, sal, sal-(sal*0.20) as “Monthly Deduction of 20%”, sal-(sal*0.20))*12 as “Annual Salary After Deduction” from emp;

4) Display unique job in each department number.

Ans : select distinct(job), deptno from emp;

5) Calculate the below columns for all employees.

Employee Name Monthly Salary 20% Deduction Deducted Salary Annual Salary after Deduction

Ans: select ename as “Employee Name”, Sal as “Monthly Salary”,
sal-(sal*0.20) as “20% Deduction”, sal-(sal-(sal*0.20)) as “Deducted Salary”,(sal-(sal*0.20))*12 as “Annual Salary After Deduction” from emp;6) Display the below sentences for all employees. I can’t work with SMITH because he works in the department’s no. 20.

Ans : select ename || ‘ can’||q'<‘t>’ || ‘ work with SMITH ‘ || ‘because he works in department’  ||q'<‘s>’ || ‘ no ‘ ||deptno from emp where ename!=’SMITH’;

7) List all employee name and total salary, they are paid 500 rupees and balance salary. Provide proper heading to all 3 columns.

Ans : select ename as “Name”, sal as “Total Salary”, 500 as “Paid Rs 500”,  sal-500 as “Balance Salary” from emp;

SQL Tutorial

SQL is a standard Query language for storing, manipulating and retrieving data in databases.

SQL provides statements for a variety of tasks, including:
-Querying data
-Inserting, updating, and deleting rows in a table
-Creating, replacing, altering, and dropping objects
-Controlling access to the database and its objects
-Guaranteeing database consistency and integrity

Our SQL tutorial will teach you how to use SQL in: Oracle database systems.

SQL Statements: 

Data Manipulation Language (DML):
Fetching data from a database, add new rows, changes existing rows, delete an unwanted row from tables in the databases, respectively. known as Data Manipulation languages (DML)
Statements:
SELECT
INSERT
UPDATE
DELETE
MERGE
Data Definition Language (DDL):
Sets up, changes and removes data structures from tables, known as
data definition languages (DDL)
Statements:
CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT

Data Control Language (DCL):
Provides or removes access rights to Oracle Database
Statements :
GRANT
REVOKE

Data Transaction Control (DTC) :
Manages the changes made by DML Statements. Changes to the data can be grouped together into logical transactions.
Statements:
COMMIT
ROLLBACK
SAVEPOINT