Homework Answers
1.
select COUNT(*) as number_of_Different_Customers from CUSTOMER
where name in (Select distinct cust_name from Transaction)
2.
Select top 1 name from TRAVEL_AGENT
order by age desc
3.
select T.travel_agent_name,count(*) as totalTransactions from TRANSACTION T
where T.amount_paid > 1000
group by T.travel_agent_name
Add Answer to:
Consider the following three relations: TRAVEL_AGENT (name, age, salary) CUSTOMER (name, departure_city, destination, journey_class) TRANSACTION (number,...
Consider the following three relations: TRAVEL_AGENT (name, age, salary) CUSTOMER (name, departure_city, destination, journey_class) TRANSACTION (number,...
Consider the following three relations: TRAVEL_AGENT (name, age, salary) CUSTOMER (name, departure_city, destination, journey_class) TRANSACTION (number, cust_name, travel_agent_name, amount_paid) Write SQL statements to answer the following questions. 1. Display the names and salaries of all travel agents who did not arrange journeys for customer “John Smith”, in ascending order of salary. 2. Display the names of travel agents who have five or more transactions. 3. Display the names of all travel agents who have arranged at least ten journeys to...
3. Consider the following relations: salesperson(emp#, name, age, salary); key is emp# order(order#, cust#, emp#, amount);...
3. Consider the following relations: salesperson(emp#, name, age, salary); key is emp# order(order#, cust#, emp#, amount); key is order# customer(cust#, name, city, industry-type); key is cust# Show the SQL expressions for the following queries: a. Get all the information of customers who have placed at least one order b. Get all the information of customers who have placed more than one order. c. Get all the information of customers who have placed an order with the salesperson, Scott Tiger. d....
31. (15 pts) Consider the following relations and relationship: Student (studentID, name, address, gender, major) Course...
31. (15 pts) Consider the following relations and relationship: Student (studentID, name, address, gender, major) Course (courselD, title, hour, department) Enrollment (StudentID, courselD, date Write the SQL statements to perf orm the following operations: List all the students' information for those majoring in "Computer Science". (2 pts) List the majors, and the numbers of the students in every major. (2 pts) List the departments, and the total number of hours of the courses offered by every department. (2 pts) List...
Consider the relational database schema for a company below. EMPLOYEE/NAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN,...
Consider the relational database schema for a company below. EMPLOYEE/NAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNA) TA DEPARTMENT(DNAME, DNUMBER. MESSINS, MGRSTARTDATE) DEPT_LOCATIONS(DNUMBER. DLOCATION PROJECT(PNAME, PNUMBER. PLOCATION, DNLIM) WORKS_ONCESSN.PNG, HOURS) DEPENDENTESSN, DEPENDENT-NAME, SEX, BDATE, RELATIONSHIP) Write SQL statements for the following queries: a) List the names of those employees who work in the "Production" department (6 marks). b) Find the maximum salary, minimum salary, and the average salary among employees who work for the "Production department (6 marks). Count the...
SQL Queries 1) Use the following relations to answer the problems below. Person(SSN, Age, Name) P...
SQL Queries 1) Use the following relations to answer the problems below. Person(SSN, Age, Name) Professor(SSN, Rank, Specialty) GraduateStudent(SSN, DegreeProgram, AdvisorSSN, DepartmentNumber) Project Number, StartDate, EndDate, Budget, SponsorOrganizationName) DepartmentNumber, Name, Abbreviation, MainOffice, ChairmanSSN) WorksFor(ProfessorSSN, Department Number, TimePercentage) WorksOn(GraduateStudentSSN, ProjectNumber, SupervisingProfessorSSN) Retrieve the Name and Specialty of all Professors who do not supervise work projects. This solution must meet the following conditions: D a. Uses at most one inner joirn b. Uses at most one outer join cNo subqueries al d....
consider the following schema: Product(pid, name, type, manufacturer, price) key: pid Buys(cid, pid) key: cid and...
consider the following schema: Product(pid, name, type, manufacturer, price) key: pid Buys(cid, pid) key: cid and pid together Customer(cid, cname, age, gender) key: cid 1) write the following query in relational algebra; find the names of all customers who have purchased all products manufactured by sears. 2)write the following in sql: find the names of all the customers who have not purchased the most expensive product. 3)write the following query in sql: find the best selling products (in terms of...
consider the following schema: Product(pid, name, type, manufacturer, price) key: pid Buys(cid, pid) key: cid and...
consider the following schema: Product(pid, name, type, manufacturer, price) key: pid Buys(cid, pid) key: cid and pid together Customer(cid, cname, age, gender) key: cid 1) write the following query in relational algebra; find the names of all customers who have purchased all products manufactured by sears. 2)write the following in sql: find the names of all the customers who have not purchased the most expensive product. 3)write the following query in sql: find the best selling products (in terms of...
Write following queries given these schemas: (FK stands for Foreign Key) Customer = {customerID, firstName, lastName,...
Write following queries given these schemas: (FK stands for Foreign Key) Customer = {customerID, firstName, lastName, income, birthDate} Account = {accNumber, type, balance, branchNumberFK-Branch} Owns = {customerIDFK-Customer, accNumberFK-Account} Transactions = {transNumber, accNumberFK-Account, amount} Employee = {sin, firstName, lastName, salary, branchNumberFK-Branch} Branch = {branchNumber, branchName, managerSINFK-Employee, budget} Focus should be on using: Order By to sort data Set Operators to union/intersect multiple tables Join Operator to join multiple tables Aggregations and Group By to aggregate data Subqueries 3.15 Customer ID, first...
2) Consider the database schema on the relations (where key attributes have been underlined): PROJECTS(Number, Department,...
2) Consider the database schema on the relations (where key attributes have been underlined): PROJECTS(Number, Department, ProjectName) EMPLOYEES(Number, Surname, FirstName, Department) ALLOCATIONSEmplovee, Project, Function, Date) NOTE: relation ALLOCATIONS stores the registration number (attribute Employee) of employees that are assigned to a given project (attribute Project is the number of the project, not the name of the project), the function the employee has in that project (i.e., technician, manager, etc.) and the date when the employee has been assigned to that...
Part II: Group-by and subqueries The following questions use the Spy and Sailors database. Write the...
Part II: Group-by and subqueries The following questions use the Spy and Sailors database. Write the SQL for the following queries. 1) List the sailor id and name for each sailor who has reserved two different red boats. Make sure that each sailor id/name is listed at most once. You must use group by/having. 2) List the missions and their status for each team. 3) For each country in agent, list the country and the count of agents in the...
3. Consider the following relations: salesperson(emp#, name, age, salary); key is emp# order(order#, cust#, emp#, amount); key is order# customer(cust#, name, city, industry-type); key is cust# Show the SQL expressions for the following queries: a. Get all the information of customers who have placed at least one order b. Get all the information of customers who have placed more than one order. c. Get all the information of customers who have placed an order with the salesperson, Scott Tiger. d....
31. (15 pts) Consider the following relations and relationship: Student (studentID, name, address, gender, major) Course (courselD, title, hour, department) Enrollment (StudentID, courselD, date Write the SQL statements to perf orm the following operations: List all the students' information for those majoring in "Computer Science". (2 pts) List the majors, and the numbers of the students in every major. (2 pts) List the departments, and the total number of hours of the courses offered by every department. (2 pts) List...
Consider the relational database schema for a company below. EMPLOYEE/NAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNA) TA DEPARTMENT(DNAME, DNUMBER. MESSINS, MGRSTARTDATE) DEPT_LOCATIONS(DNUMBER. DLOCATION PROJECT(PNAME, PNUMBER. PLOCATION, DNLIM) WORKS_ONCESSN.PNG, HOURS) DEPENDENTESSN, DEPENDENT-NAME, SEX, BDATE, RELATIONSHIP) Write SQL statements for the following queries: a) List the names of those employees who work in the "Production" department (6 marks). b) Find the maximum salary, minimum salary, and the average salary among employees who work for the "Production department (6 marks). Count the...
SQL Queries 1) Use the following relations to answer the problems below. Person(SSN, Age, Name) Professor(SSN, Rank, Specialty) GraduateStudent(SSN, DegreeProgram, AdvisorSSN, DepartmentNumber) Project Number, StartDate, EndDate, Budget, SponsorOrganizationName) DepartmentNumber, Name, Abbreviation, MainOffice, ChairmanSSN) WorksFor(ProfessorSSN, Department Number, TimePercentage) WorksOn(GraduateStudentSSN, ProjectNumber, SupervisingProfessorSSN) Retrieve the Name and Specialty of all Professors who do not supervise work projects. This solution must meet the following conditions: D a. Uses at most one inner joirn b. Uses at most one outer join cNo subqueries al d....
2) Consider the database schema on the relations (where key attributes have been underlined): PROJECTS(Number, Department, ProjectName) EMPLOYEES(Number, Surname, FirstName, Department) ALLOCATIONSEmplovee, Project, Function, Date) NOTE: relation ALLOCATIONS stores the registration number (attribute Employee) of employees that are assigned to a given project (attribute Project is the number of the project, not the name of the project), the function the employee has in that project (i.e., technician, manager, etc.) and the date when the employee has been assigned to that...
Most questions answered within 3 hours.
-
Oxidative phosphorylation is achieved through chemiosmotic
coupling, which turns chemical energy into osmotic potential energy
that...
asked 51 minutes ago -
Hollywood Shoes would like to maintain their cash account at a
minimum level of $64,000, but...
asked 50 minutes ago -
Proteins CtrA-P, DnaA, and GcrA behave similarly to what
molecule in the Eukaryotic Cell Cycle?
asked 53 minutes ago -
TRUE OR FALSE
In case of a competitive tender procedure, in the request for
quotation, the...
asked 1 hour ago -
I am a little behind please provide some explanation, will
provide thumbs up.
This question considers...
asked 1 hour ago -
Let say the utility function as U(X,Y) = lnX + Y. Show that the
marginal rate...
asked 1 hour ago -
A current of I=8.0A is flowing in a typical extension cord of
length L=3.00m. The cord...
asked 1 hour ago -
1. When balancing the equation H2SO4 (aq) + NaOH (aq) →
Na2SO4(aq) + H2O(aq), would you...
asked 1 hour ago -
Provide a definition of “classification hierarchy” and
illustrate your definition with an example of how and...
asked 1 hour ago -
1. Describe the four main green solvents. Provide one example of
the use of each solvent....
asked 1 hour ago -
International ethics focus on:
1) exploitation of labor
2)corruption
3) environmental impact
4) none of the...
asked 1 hour ago -
Costing Models You work for XYZ trucking and your boss has asked
you to figure out...
asked 1 hour ago