حل اسئلة استعلام قواعد البيانات قسم الحاسوب الجامعة المستنصرية نموذج رقم1

 

Question #1 / [10 marks] Chose the correct answer.

1- ----------- statements that create and modify database objects.

a) DDL b) DML c) SDL d) DCL e) DQL f) VDL

2- A view is mapped to ------------ statement.

a) SELECT b) INSERT c) UPDATE d) CREATE TABLE

3- The PL/SQL blocks that have a name, a parameters, can called -----------.

I) Trigger II) Procedure III) Function IV) Package

a) IV b) I & II c) I & IV d) I, II, & III e) II, III, & IV

4- --------- provide fast searching of tables based on one or more key columns.

a) Order by b) Group by c) Index d) Sequence

5- Which of the following do you need to consider when you make a table in SQL?

a) Data types b) Primary keys c) Default values d) All of the above

6- The --------------- define and initialized the variables and cursor used in the block.

a) Declaration Section b) Executable Section c) Executable Handling

d) Exception Section e) Exception Handling.

7- SQL provides the ---------- keyword, which can be used to assign meaningful column names to the results of queries.

a) FROM b) AS c) LIKE d) BETWEEN

8- SELECT ----------- is used if a user wishes to see no duplicate columns in a query.

a) VAL( ) b) COUNT ( ) c) DISTINCT d) SYSDATE

9- To define a constant, ----------- can used.

a) VSal Number Constant = 120; b) VSal Number Constant := 120; c) Vid Date Constant;

d) VSal Constant Number = 120; e) VSal Constant Number := 120;

10- With DROP behavior option ---------, all constraints, views, and other elements that references the table being dropped are also dropped along with the table itself.

a) modify b) delete c) restrict d) cascade

Question #2 / [10 marks] Write appropriate DDL statements to define the relational database model.

Sol//

CREATE TABLE EMPLOYEE ( Ename VARCHAR(50), Ssn CHAR(9) PRIMARY KEY, Bdate DATE, Address VARCHAR(100), Dnumber INT, FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber) ); CREATE TABLE DEPARTMENT ( Dnumber INT PRIMARY KEY, Dname VARCHAR(50), Dmgr_ssn CHAR(9), FOREIGN KEY (Dmgr_ssn) REFERENCES EMPLOYEE(Ssn) ); CREATE TABLE DEPT_LOCATIONS ( Dnumber INT, Dlocation VARCHAR(50), PRIMARY KEY (Dnumber, Dlocation), FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber) ); CREATE TABLE PROJECT ( Pnumber INT PRIMARY KEY, Pname VARCHAR(50), Plocation VARCHAR(50), Dnum INT, FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber) ); CREATE TABLE WORKS_ON ( Ssn CHAR(9), Pnumber INT, Hours DECIMAL(5,2), PRIMARY KEY (Ssn, Pnumber), FOREIGN KEY (Ssn) REFERENCES EMPLOYEE(Ssn), FOREIGN KEY (Pnumber) REFERENCES PROJECT(Pnumber) );

Question #3 / [10 marks] Having the following tables,

A. Specify the following SQL statements. [5 marks]

1. Delete (HourFee) column from company table.

2. Find summation, average, MIN & MAX hour fee for all employees in each project.

3. Display employee names, jobs for employees that work in the same project of employee (Maria D Alonzo) and have job hours greater than employee that having (41.4) job hours.

4. Add new information, <25, 'Evergreen', 120, 'Sam'> to project number & name, employee number & name in the table.

5. Display employee name and project name for employees that have not hour fee.

Sol//

**1. Delete (HourFee) column from company table.**

ALTER TABLE company DROP COLUMN HourFee;

**2. Find summation, average, MIN & MAX hour fee for all employees in each project.**

SELECT ProjectNo, ProjectName,
  SUM(HourFee) AS Total_Fee,
  AVG(HourFee) AS Average_Fee,
  MIN(HourFee) AS Min_Fee,
  MAX(HourFee) AS Max_Fee
FROM company
GROUP BY ProjectNo, ProjectName;

**3. Display employee names, jobs for employees that work in the same project 
   of employee (Maria D Alonzo) and have job hours greater than employee 
   that having (41.4) job hours.**

SELECT EmpName, JopType
FROM company
WHERE ProjectNo = (
  SELECT ProjectNo
  FROM company
  WHERE EmpName = 'Maria D Alonzo'
)
AND HourFee > 41.4;

**4. Add new information, <25, 'Evergreen', 120, 'Sam'> to project number & name, 
   employee number & name in the table.**

INSERT INTO company (ProjectNo, ProjectName, Employeeld, EmpName, HourFee)
VALUES (25, 'Evergreen', 120, 'Sam', NULL);  -- Assuming NULL for missing HourFee

**5. Display employee name and project name for employees that have not hour fee.**

SELECT EmpName, ProjectName
FROM company
WHERE HourFee IS NULL;

B. Show the result of the following queries in SQL, if it applied to the database tables present previously. [5 marks]

1. SELECT EmpName, JobType, HourFee FROM company

WHERE HourFee = (select MIN (HourFee) from company);

2. SELECT ProjectName, MIN (JobHours) , MAX (HourFee) FROM company

GROUP BY ProjectName;

3. SELECT EmpName, JobType, HourFee + JobHours FROM company;

4. SELECT ProjectNo, AVG(JobHours) FROM company

WHERE AVG(JobHours) > 40

GROUP BY ProjectNo;

5. SELECT EmpName, JobType FROM company

WHERE (ProjectName, JobHours) IN (select ProjectName, JobHours from company

where EmployeeId=105 and ProjectNo=25);

Sol//

1-Result:

EmpName	JobType	HourFee
Darlene M. Smithson	DSS Analyst	45.95

2-Result:

ProjectName	MIN(JobHours)	MAX(HourFee)
Evergreen	23.8	45.95
Starflight	24.6	45.95

3-Result:

EmpName	JobType	HourFee + JobHours
Elec. Engineer	June E	NULL
Darlene M. Smithson	DSS Analyst	69.55
Michael M. Smithson	DSS Analyst	65.35
Maria D Alonzo	Programmer	69.35

4-No results in this case, as the average JobHours for all projects is less than 40.

5-Result:

EmpName	JobType
Darlene M. Smithson	DSS Analyst

Question #4 / [10 marks]

Give the EMP table. Write PL/SQL block to retrieve employee name that have Job equal to SCOTT job and then set the salary of this employee to 4000.

EMP table

EMPNO  ENAME  JOB  SAL  COMM  MGR  HEIRDATE  DEPTNO

Sol//

DECLARE
  emp_name VARCHAR2(100);
  emp_id NUMBER;
BEGIN
  -- Find employee ID for the SCOTT job
  SELECT empno INTO emp_id
  FROM emp
  WHERE job = 'SCOTT';

  -- Check if an employee with the SCOTT job exists
  IF emp_id IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Employee with job ''SCOTT'' not found.');
  ELSE
    -- Get employee name
    SELECT ename INTO emp_name
    FROM emp
    WHERE empno = emp_id;

    -- Update salary to 4000
    UPDATE emp
    SET sal = 4000
    WHERE empno = emp_id;

    DBMS_OUTPUT.PUT_LINE('Employee ' || emp_name || ' (ID: ' || emp_id || ') salary updated to 4000.');
  END IF;
END;
/

Subject: Structure query language (SQL) / Practical

NOTE: Answer one of these questions.

Q1: Q1: About the Employ table below

A. Display the number of Employs, average of salary, and the difference between highest and lowest salary of the employees of the departments

B. Show the result of the SQL statements

Select Employ_name,

UPPER (CONCAT (SUBSTR (Employ_name, 1, 4),’_it’))

Where Employ_dep_id=10

Sol//

A. Displaying Department Statistics

SELECT Employ_dep_nam AS Department,
       COUNT(*) AS Number_of_Employees,
       AVG(Employ_salary) AS Average_Salary,
       MAX(Employ_salary) - MIN(Employ_salary) AS Salary_Difference
FROM Employ
GROUP BY Employ_dep_nam;

B. Retrieving Specific Employee Data

The provided SQL statement:

SELECT Employ_name,
       UPPER(CONCAT(SUBSTR(Employ_name, 1, 4), '_it'))
WHERE Employ_dep_id = 10;

This query would return the following results:

Employ_name	UPPER(CONCAT(SUBSTR(Employ_name, 1, 4), '_it'))
Mohamod	MOHAM_it
Mosa	MOSA_it

Q2: Write a PL/SQL program to count the commotion for Car_id =20 in Cars table below :

Sol//

DECLARE    v_car_id Cars.Car_id%TYPE := 20;    v_commotion Cars.Car_commotion%TYPE;    v_count NUMBER := 0;BEGIN    -- Retrieve commotion for Car_id = 20    SELECT Car_commotion INTO v_commotion    FROM Cars    WHERE Car_id = v_car_id;
    -- Count the commotion    IF v_commotion IS NOT NULL THEN        v_count := v_count + 1;    END IF;
    -- Output the count    DBMS_OUTPUT.PUT_LINE('Commotion count for Car_id ' || v_car_id || ': ' || v_count);EXCEPTION    WHEN NO_DATA_FOUND THEN        DBMS_OUTPUT.PUT_LINE('No data found for Car_id ' || v_car_id);    WHEN OTHERS THEN        DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);END;/