MIS
9th Edition
ISBN: 9781337681919
Author: BIDGOLI
Publisher: Cengage
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
error_outline
This textbook solution is under construction.
Students have asked these similar questions
student(sid, sname, sex, age, year, gpa)
dept(dname, numphds)
prof(pname, dname)
course(cno, cname, dname)
major(dname, sid)
section(dname, cno, sectno, pname)
enroll(sid, grade, dname, cno, sectno)
Write the following SQL query for the schema above:
Print the name(s) and sid(s) of the student(s) enrolled in the most classes.
student(sid, sname, sex, age, year, gpa)
dept(dname, numphds)
prof(pname, dname)
course(cno, cname, dname)
major(dname, sid)
section(dname, cno, sectno, pname)
enroll(sid, grade, dname, cno, sectno)
Write the following SQL queries for the schema above:
Print the course names, course numbers and section numbers of all classes with less than six students enrolled in them.
Print the name(s) and sid(s) of the student(s) enrolled in the most classes.
Print the names of departments that have one or more majors who are under 18 years old.
Print the names and majors of students who are taking one of the College Geometry courses.
For those departments that have no majors taking a College Geometry course, print the department name and the number of PhD students in the department.
Print the names of students who are taking both a Computer Sciences course and a Mathematics course.
For each department that has one or more majors with a GPA under 1.0, print the name of the…
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.
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- creat the following tables and insert your own values: emp(eno, ename, bdate, title, salary, dno) proj(pno,pname,budget,dno) dept(dno,dname,mgreno) working(eno,pno,resp,hours)arrow_forwardYou are given the schema of a table with its columns inside the bracket as follows: STUDENT(Name, Student_id, Major, Marks, Email) (The Primary Key is underlined) Write an SQL query for the following tasks: (Hint: the queries are nested) 1. Write an SQL query to retrieve the name, student id and the maximum marks of students of each major where that maximum marks is greater than all students with CS major. 2. Write an SQL query to retrieve the name, student id and the maximum marks of students with ECE major where that maximum marks is greater than all students with CS major.arrow_forwardGiven the following relational schema (Hint: the bold text represents a primary key), Department (dept_code, dept_name, emp_num, dept_address, dept_ext)Student(stu_num, stu_fname, stu_lname, stu_dob, stu_class, stu_gpa, emp_num, dept_code) Write the SQL statement to list all department names their highest GPA of the student in the department. The result must have two columns: dept_name and "max GPA" and is ranked from the highest GPA value to the lowest onearrow_forward
- 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 job ID and maximum salary of the employees where maximum salary is greater than or equal to $4000. b. Write a query to get the average salary for all departments employing more than 10 employees.arrow_forwardConsider the following schema:instructor(ID, name, dept_name, salary)department(dept_name, building, college, budget)What is the correct SQL statement to perform a query to find the names and average salaries of departments in the college of ‘Science’ whose average salary is greater than 75000? (a) select dept_name, avg (salary)from instructor natual join departmenthaving college = ‘Science’group by dept_namewhere avg (salary) > 75000;(b) select dept_name, avg (salary)from instructor natual join departmentwhere college = ‘Science’group by dept_namewhere avg (salary) > 75000;(c) select dept_name, avg (salary)from instructor natual join departmentwhere college = ‘Science’group by dept_namehaving avg (salary) > 75000;(d) select dept_name, avg (salary)from instructor natual join departmenthaving college = ‘Science’group by dept_namehaving avg (salary) > 75000;(e) select dept_name, avg (salary)from instructor natual join departmentwhere college = ‘Science’and avg (salary) >…arrow_forwardKindly use the hospital relational schema provided below to solve these questions. 2. List Registration numbers of all patients in H02, ward W18 who have been diagnosed with "Flu" but for whom "Chest X-ray" test has not been ordered.a) First get a table with only one column RegistrationNumber, which lists all patients from W18 of H02 who have been diagnosed with flu.b) Second, get a table with only one column RegistrationNumber, which lists all patients from W18 of H02 who have had a test with Tname="Chest X-ray".c) Finally, take the set difference.arrow_forward
- 6. use sql and the folloing data as shown below to Display Warehouse Id, warehouse name, product category Id, product category name, and the lowest product standard cost for this combination. In your result, include the rows that the lowest standard cost is less then $200. Also, include the rows that the lowest cost is more than $500. Sort the output according to Warehouse Id, warehouse name and then product category Id, and product category name.arrow_forwardWorksOn Database: Employee (EmployeeNumber, EmployeeName, BirthDate, Title, Salary, DepartmentNumber) Project (ProjectNumber, ProjectName, Budget, DepartmentNumber) Department (DepartmentNumber, DepartmentName, ManagerNumber) WorksOn (EmployeeNumber, ProjectNumber, Responsibility, Hours) Questions: 1) Write an SQL query that returns the project number and name for projects with a budget no greater than $1,050,000. 2) Write an SQL query that returns all works on records where hours worked is less than 5 and the responsibility is 'Manager'. 3) Write an SQL query that returns the employees (number and name only) who have a title of 'EE' or 'SA' and make more than $50,000. 4) Write an SQL query that returns the employees (name and salary only) in department 'D1' ordered by decreasing salary. 5) Write an SQL query that returns the departments (all fields) ordered by descending department name.arrow_forwardUsing sql code, Create a table that shows a unique list of potential politically exposed and sanctioned customers from a csv file called Santionedlist as well as the number of times each customer record from the table CombinedCustomerInfo matched the Sanctionedlist. Below are the report output column names: Customer Id First name Last Name Country of Origan (from Sanctionedlist) Source System Name Master Record [Y/N] --Hint: Should always be Y Number of Matches PEP or Sanctioned criteria: OneSurance has an external list that provides an international list of PEP and Sanctioned individuals. A customer is regarded as a potential PEP or sanction individual if his or her First, Last Name, Date of Birth and Country of birth matches 1 or more of the candidates listed in the SanctionedList.arrow_forward
- using sql develpper or sql live oralce Create the DONORS table with the following columns: donor_id ( PRIMARY KEY), donor_name (Not Null), donor_age. Then Create DONATIONS table with the following columns: pledge_id (PRIMARY KEY), pledge_date, amount_pledged, amount_paid, Is_paid, donor_id that REFERENCES to DONORS table (donor_id). 2- Populate every relation with three rows. 3- Now, your supervisor has asked you to modify the donor_age column in the DONORS table. He wants the column to be configured to accept a value that must be >=20. Which statement should you issue to accomplish this task? 4- Create a Trigger to update Is_paid column to 1 when the amount_paid equal to amount_pledged.arrow_forwardStudent(Stud_Id, name, addr, sex, DOB, country , major) Lecturer(Lect_id, name, sex, department, tel_no) Course(Course_code, description, credit_hr, department) Registration(Stud_id, course_code, room_no, lect_id, time) Write the correct relational algebra for the following queries: Display student id, name and sex for all male students from the country of ‘Singapore’. Display student id, name, and major for all students that majoring in ‘CS’ or ‘ENG’. Display detail registration listing for courses that have more than 3 credit hours. Display student name and course description for all students taking courses offered by ‘IS’ department.arrow_forwardConsider 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) Write a query to get the difference between the highest and lowest salaries. Write a query to find the manager ID and the salary of the lowest-paid employee for that manager. Write a query to get the department ID and the total salary payable in each department. Write a query to get the total salary, maximum, minimum, average salary of employees (job ID wise), for department ID 90 only. Write a query to get the average salary for all departments employing more than 10 employees.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrNp Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage