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- Find all employees who work in ministery of foreign affairs And their age less than 33. 2- find the ministries that have at least 6 department and order them by their address.

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- Find all employees who work in ministery of foreign affairs And their age less than 33. 2- find the ministries that have at least 6 department and order them by their address.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

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.
51. The given SQL statement is intended to either (1) list the customer numbers, customers names, and headquarter cities of customers headquartered in Los Angeles or (2) satisfy the two conditions of having a customer number higher than 1000 and being headquartered in Denver. SELECT CUSTNUM, CUSTNAME, HQCITY FROM CUSTOMER WHERE HQCITY= 'Los Angeles" Which set of lines should be added to the end of the statement to make the statement work as intended? A: (CUSTNUM>1000 AND HQCITY-'Denver'); B: AND (CUSTNUM>1000 AND HQCITY='Denver*); C:OR CUSTNUM>1000 OR HQCITY='Denver": D:OR (CUSTNUM> 1000 AND HQCITY='Denver*);
Consider the following schema(keys are underline); Product(pid,name,type,manufacturer,price), (Buys(cid,pid), Customer(cid,cname,age,gender) (a) write the following query in relational algebra. Find the names of all customers who have purchased all products manufactured by Sears. (b)Write the following query in SQL: Find the names of all customer who have not purchased the most expensive product. (C)Write the following query in SQL. Find the names and cid of all customers who have purchased the second most expensive product. You can assume that no two products have the same price.
a) from university database , Write the following queries in SQL Find the titles of courses in the Comp. Sci. department that have 3 credits Find all instructors earning the highest salary (there may be more than one with the same salary) Find all courses that have never been offered (i.e., do not occur in the section relation). b) given the following tables( instructor, teaches) what is the output of the following query select i.name from instructor as i, teaches as t where i.ID=t.ID and semester=’ Fall’ and year=2017;
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.
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.
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
Consider the following relational schema and answer the following questions. User (email, name, address, householdSize) a. Express in relational algebra the query that finds all pairs of users where the two people both claim to have a household size 2 and have the same address and returns their names and the common address. b. Express the above query in SQL. c. Write in SQL the query that finds the users whose household size is at least 50% more than the average household size and returns their name and household size, sorted by household size. (Hint: decompose the problem into sub-problems, and you may use a view to capture an intermediate result.) d. Write in SQL the query that finds all users each having a household size different from the total number of users having the same address as him or her. (Hint: again, decompose the problem into sub-problems, and you may use a view to capture a intermediate result.)
can you help me iwth these two queries in mySQL. Write a SQL statement to create a table job_history including columns employee_id, start_date, end_date, job_id and department_id and make sure that, the employee_id column does not contain any duplicate value at the time of insertion and the foreign key column job_id contain only those values which are exists in the jobs table. Write a SQL statement to create a table employees including columns employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary, commission, manager_id and department_id and make sure that, the employee_id column does not contain any duplicate value at the time of insertion and the foreign key columns combined by department_id and manager_id columns contain only those unique combination values, which combinations are exists in the departments table.
2. Express the following queries in SQL: a) List first name, last name and department ID of all employees ordered according to the hire date in descending order. b) Display the details (Employee ID, First Name, Salary, department ID) of employees working in department number 90.
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 the following tables. Primary keys are identified after each table. Hotel(Hnum,HName,Location) PK = HNum Room(RNum,HNum,Type,View,Price) PK = RNUM , HNum Booking(id,HNum,GNum,RNum,DateFrom,DateTo) PK = id Guest(GNum,Fname,Lname,Gender,DOB,City) PK = GNum Write SQL queries AND Relational algebra expressions (using the relational algebra operators: ρ, σ, π, , υ, ∩, sum, avg, count) for each of the following queries a) List all information about the rooms that have a price below the average price of all single rooms b) For each Hotel, List the total number of rooms c) List the names of hotels that have no booking in them d) List the first name and last name of Parisian guests who book a double room in Paris PLEASE ANSWER THE QUESTION BY WRITING SQL QUERIES AND RELATIONAL ALGEBRA EXPRESSION. Thank you.