QI) Consider the employees database, where the primary keys are underlined. Give an expression in SQL for each of the following queries. Employee(ID, name, age, address, salary, departmet, position, ministry_name) Ministry(ministry_name, address,No of_employee, No of_dept., category) 1- update the record data of the ministry of education in ministry table. 2- Find the employees who live in Basra and work as a driver in Ministry of finance. 3- Find the name of employees who work as a manger in ministries that have more than 100 employee and lies in baghdad city.

QI) Consider the employees database, where the primary keys are underlined. Give an expression in SQL for each of the following queries. Employee(ID, name, age, address, salary, departmet, position, ministry_name) Ministry(ministry_name, address,No of_employee, No of_dept., category) 1- update the record data of the ministry of education in ministry table. 2- Find the employees who live in Basra and work as a driver in Ministry of finance. 3- Find the name of employees who work as a manger in ministries that have more than 100 employee and lies in baghdad city.

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter11: Database Performance Tuning And Query Optimization

Section: Chapter Questions

Problem 7RQ: How is the processing of SQL DDL statements (such as CREATE TABLE) different from the processing...

See similar textbooks

Similar questions

Q1) Consider the employees database , where the primary keys are underlined. Give an expression in SQL for each of the following queries. Employee(ID. name, age, address, salary, departmet, position,ministry_name) Ministry(ministry_name, address,No_of _employee, No_of_dept., category) 1- Find all employees who live in baghdad and work in ministery of foreign affairs And older than all the employees who work in the consuls department. 2- Find the name of employees who work as a counselor in ministries that have more than 5 departments . 3- Insert a new employee (o the database.
Simplify the following query (WHERE clause), expressed in SQL, on the example database above: SELECT ENAME, PNAME FROM EMP, ASG, PROJ WHERE (DUR > 12 OR RESP = "Analyst") AND EMP.ENO = ASG.ENO AND (TITLE = "Elect. Eng." OR ASG.PNO < "P3") AND (DUR > 12 OR RESP NOT= "Analyst") AND ASG.PNO = PROJ.PNO
Q1/ Consider the fruit database with fruit and market tables, where the primary keys are underlined. Give an expression in SQL for each of the following queries. Fruit(ID, fruit_name, fruit_color, price_KG) Market(market name, city, street, fruit_name) 1- Find the names of all red fruits ordered by price 2- Find the names of markets that sell banana in Atlanta city
Consider a scenario in which the SQL statement is of the form SELECT DISTINCT c1, c2, c3 from T. Why might it make sense to create an index to optimize this query?
Given the following relational schema (Hint: the bold text represents a primary key), Department (dept_code, dept_name, emp_num, dept_address, dept_ext)Course (course_code, course_name, crs_credits, dept_code)Class (class_code, course_code, class_section, class_time, class_room, emp_num)Professor (emp_num, emp_fname, emp_lname, emp_dob, dept_code, prof_ext) Write the SQL statement to show course name with the code beginning with "CIS" and their class information. The result must consist of course_code, course_name, class_code, and class_section.
Given the following relational schema (Hint: the bold text represents a primary key), Department (dept_code, dept_name, emp_num, dept_address, dept_ext)Course (course_code, course_name, crs_credits, dept_code)Class (class_code, course_code, class_section, class_time, class_room, emp_num)Professor (emp_num, emp_fname, emp_lname, emp_dob, dept_code, prof_ext) Write the SQL statement to show all class code, course name, and department name for the courses with 4 credits
Consider the employee database of Figure ??, where the primary keys areunderlined. Give an expression in SQL for each of the following queries.a. Find the names and cities of residence of all employees who workfor First Bank Corporation.b. Find the names, street addresses, and cities of residence of all employeeswho work for First Bank Corporation and earn more than$10,000.c. Find all employees in the database who do not work for First BankCorporation.d. Find all employees in the database who earn more than each employeeof Small Bank Corporation.e. Assume that the companies may be located in several cities. Find allcompanies located in every city in which Small Bank Corporation islocated.f. Find the company that has the most employees.g. Find those companies whose employees earn a higher salary, onaverage, than the average salary at First Bank Corporation.
Given the Bank database that contains the following tables (primary keys are underlined and foreign keys are preceded with #): Agency(agencyNumber, agencyName, city, assets) Client (clientNumber, firstName, lastName, clientCity) Account(accountNumber, #agencyNumber, #clientNumber, balance) Loan(loanNumber, #agencyNumber, #clientNumber, amount ) Without using DISTINCT, write the SQL query equivalent to the following one SELECT DISTINCT clientNumber FROM Account WHERE balance < 1000 OR balance > 100000 ;
Consider the following relational schema:Course (cid, cname, dept, credit)Student (sid, sname, dept, gpa)Enroll (cid, sid, grade, semester) The meaning of these relations is straightforward and the keys are underlined. Write thefollowing queries in SQL. d) Find the name s of students who took courses offered by CS department and noother department. e) Find the names of all classes that have 10 or more students enrolled.
Consider the following schema and write SQL query for given statements. Student ( regNo, sName, sCity, sCGPA, sAge)Course ( cCode, cName, credit_hours)Enrollment (regNo, cCode, semester,quality_points,Grade) 1- Display 2nd row of Student table using Cursors. 2- Display all records of Enrollment table which are placed at even rows. 3- Execute given query in SQL and do as directed.a) UPDATE STUDENT SET ECITY = ‘ISB’ WHERE EID>101b) Count how many records have been updated in above query using cursor. 4- Write a mechanism which must not allow user to delete more than 1 records using cursor and transaction.
Assume the e-commercial database shown in the Appendix. Write the following queries in (a) relational algebra and (b) SQL. State your assumptions if the semantics is not clear. 1) Find the names of all students who have not taken any courses. APPENDIX STUDENTS(student id, first_name, last _name, email, major_code, class_year) COURSES(course id, course_name, course_description, credits, department_code) ENROLLMENTS(student id, course id, semester, grade) MAJORS(major code, major_name, department_code) DEPARTMENTS(department code, department_name)
Consider the following relational schema and answer the following questions. Employee (Employee_Id, First_Name, Last_Name, Email, Phone_Number, Hire_Date, Job_Id, Salary, Commission_Pct, Manager_Id, Department_Id) a. Write a query to get the average salary and number of employees working the department 90. b. Write a query to get the highest, lowest, sum, and average salary of all employees. c. Write a query to get the number of employees with the same job. d. Write a query to get the difference between the highest and lowest salaries.

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS