Run lab_emp.sql and lab_students.sql, this will create 2 tables for you (emp and students) and will fill them with data. create table emp ( eid number(7), fname char(20), lname char(20), salary number, primary key (eid) ); insert into emp values (1111111, 'ali', 'ahmed', 3500); insert into emp values(2222222, 'asma', 'dani', 2000); insert into emp values(3333333, 'hassan', 'ali', 4500); insert into emp values(4444444, 'khaled', 'shaalan',6000); insert into emp values(5555555, 'farhad', 'kokol', 7500); insert into emp values(6666666, 'amir', 'sheikh', 10000); insert into emp values(7777777, 'rahul', 'kolap', 11000); insert into emp values(8888888, 'halim', 'ziaey', 21000); insert into emp values(9999999, 'henna', 'bubaker', 30000); create table students ( sid number, fname char(20), lname char(20), avg number, age number, primary key (sid) ); insert into students values (1111111, 'ali', 'ahmed', 76, 21); insert into students values(2222222, 'asma', 'dani', 89, 20); insert into students values(3333333, 'hassan', 'ali', 67, 23); insert into students values(4444444, 'khaled', 'shaalan',87, 24); insert into students values(5555555, 'farhad', 'kokol', 82, 19); insert into students values(6666666, 'amir', 'sheikh', 56, 18); insert into students values(7777777, 'rahul', 'kolap', 81, 22); insert into students values(8888888, 'halim', 'ziaey', 97, 21); insert into students values(9999999, 'henna', 'bubaker', 45, 18);
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.
Task 1:
Run lab_emp.sql and lab_students.sql, this will create 2 tables for you (emp and students) and will fill them with data.
create table emp (
eid number(7),
fname char(20),
lname char(20),
salary number,
primary key (eid)
);
insert into emp values (1111111, 'ali', 'ahmed', 3500);
insert into emp values(2222222, 'asma', 'dani', 2000);
insert into emp values(3333333, 'hassan', 'ali', 4500);
insert into emp values(4444444, 'khaled', 'shaalan',6000);
insert into emp values(5555555, 'farhad', 'kokol', 7500);
insert into emp values(6666666, 'amir', 'sheikh', 10000);
insert into emp values(7777777, 'rahul', 'kolap', 11000);
insert into emp values(8888888, 'halim', 'ziaey', 21000);
insert into emp values(9999999, 'henna', 'bubaker', 30000);
create table students (
sid number,
fname char(20),
lname char(20),
avg number,
age number,
primary key (sid)
);
insert into students values (1111111, 'ali', 'ahmed', 76, 21);
insert into students values(2222222, 'asma', 'dani', 89, 20);
insert into students values(3333333, 'hassan', 'ali', 67, 23);
insert into students values(4444444, 'khaled', 'shaalan',87, 24);
insert into students values(5555555, 'farhad', 'kokol', 82, 19);
insert into students values(6666666, 'amir', 'sheikh', 56, 18);
insert into students values(7777777, 'rahul', 'kolap', 81, 22);
insert into students values(8888888, 'halim', 'ziaey', 97, 21);
insert into students values(9999999, 'henna', 'bubaker', 45, 18);
Task 2:
Solve the following questions:
Question 1: (use loop)
Write a program that displays an appropriate message for a given employee based on the criteria given below use.
- if (salary <= 4000) then ‘Low Income’
- if (salary > 4000 and salary <=7000) then ‘Avg Income’
- if (salary > 7000 and salary <= 15000) then ‘Moderate Income’
- else ‘High Income’
Question 2: (use while loop)
Write a program to update the salary of all employees with salaries less than 7000: salary = salary * ((pct/100) +1). (use for loop)
Question 3: (use for loop)
Write a program which shows the average of average for students whose age are 20 or below.
Question 4:
Write a program to get all male students and add 10% to their average if the average is 80 or above and 2% if the average is less than 80.
Step by step
Solved in 2 steps with 2 images
