CREATE TABLE Employee( Eid INT PRIMARY KEY, E_name VARCHAR(20), E_Age INT CHECK (E_Age between 18 and 60), ECity VARCHAR(20), E_Street VARCHAR(20), E_House# INT, E_Basic_Salary DECIMAL(18,2), E_Bonus DECIMAL(18,2), E_Rank VARCHAR(20) CHECK (E_Rank IN('Manager','Develper','Admin')), E_Department VARCHAR(20) DEFAULT 'CS' CHECK (E_Department IN ('HR','CS','IT','Labour'))); Create Table Porjects: CREATE TABLE Project1( p_id INT , pName VARCHAR(20), p_bonus DECIMAL(18,2), duration_in_months INT, Eid INT, PRIMARY KEY(p_id,Eid), FOREIGN KEY(Eid) REFERENCES Employee(Eid)); now the question is... Display Total expenditures of company. (total expenditures = basic salary + bonus of employee + all project bonus) Display names of projects in which ‘Ahmar’ is Display total project bonus of ‘Danish’.
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
CREATE TABLE Employee( Eid INT PRIMARY KEY, E_name VARCHAR(20), E_Age INT CHECK (E_Age between 18 and 60), ECity VARCHAR(20), E_Street VARCHAR(20), E_House# INT, E_Basic_Salary DECIMAL(18,2), E_Bonus DECIMAL(18,2), E_Rank VARCHAR(20) CHECK (E_Rank IN('Manager','Develper','Admin')), E_Department VARCHAR(20) DEFAULT 'CS' CHECK (E_Department IN ('HR','CS','IT','Labour')));
Create Table Porjects:
CREATE TABLE Project1( p_id INT , pName VARCHAR(20), p_bonus DECIMAL(18,2), duration_in_months INT, Eid INT, PRIMARY KEY(p_id,Eid), FOREIGN KEY(Eid) REFERENCES Employee(Eid));
now the question is...
- Display Total expenditures of company. (total expenditures = basic salary + bonus of employee + all project bonus)
- Display names of projects in which ‘Ahmar’ is
- Display total project bonus of ‘Danish’.
- Display total expenditures of only those departments whose total expenditures are more than 1 Million. (total expenditures = sum of basic salary of all employees of that department)
- Create Employee
- Create Project
- Insert a record in employee table, ( you must not enter edepartment and ebonus).
- change basic salary of all employees, add increment of 5% to
- Delete only those employees whose salary is above
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
