Database System Concepts
Database System Concepts
7th Edition
ISBN: 9780078022159
Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher: McGraw-Hill Education
Bartleby Related Questions Icon

Related questions

Q: Display the list of students who are involved in the IT club Display the details of all the…
A: First get the studid from Club table and then get those Student details This can be done using inner…
Q: I need help with the SQL query for the following question: 1. Get top 5 list of products liked in…
A: QUERY: Creating table: CREATE TABLE Reviews(     ProductModelName text,     ProductCategory text,…
Q: Just let me know what to write for the SQL theory: Question:  Write a query to display the first…
A: Solution:   Given,
Q: Employee (empNo, firstName, lastName, address, Department (deptNo, deptName, mgrEmpNo) Project…
A: Solution- We have create an query for each of them in step by step manner.Here, we look for patterns…
Q: Write a SELECT statement that returns two columns based on the Vendors table. The first column,…
A:
Q: Write a single SQL query statement that lists the work id, worker name, task id, task name and,…
A: Definition: Designed for handling data stored in a relational database management system, SQL is a…
Q: The following SQL query is for retrieving the name of artists whose songs with an average length of…
A: The question presents an SQL query that aims to retrieve the names of artists whose songs have an…
Q: Question 11. Write the SQL statement that will implement the relational algebra expressions in the…
A: The queries are given below
Q: Design an SQL query to determine, for each SeatType in the manifest table (B, E, and EP), the total…
A: Creating SeatInfo table: create table SeatInfo( SeatType varchar(10), TicketPrice integer);…
Q: I need help with the SQL query for the following question: 1.  Get the total number of reviews of…
A: The details about the table are given with table name and different entities. Here the table name is…
Q: What does the following SQL query appear to do? SELECT category_name FROM categories WHERE…
A: The given SQL query selects the names of all categories that do not have any products associated…
Q: Create the following table in your database with the following schema: Table: Products Column Name 1…
A: As per our guidelines, only 3 sub parts will be answered. So, please repost the remaining questions…
Q: Write and execute a SQL query to list all patients whose information about race or gender has…
A: Answer: SQL Query: Method 1: select vvs.*, lag(race) over (partition by user order by age) as…
Q: Show the total number of reviews of most liked product per retailer in every city  Here is the…
A: The details about the table are given with table name and different entities. Here the table name is…
Q: 1.What is the result when the following SQL query is executed on a table named products? Choose the…
A: Answer to these problems are   AS here in 1 question we are given that there is table name products…
Q: Get a list of 10 product reviews that got review rating 5 and price more than thousand. Here is the…
A: The details about the table are given with table name and different entities. Here the table name is…
bartleby

Concept explainers

SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prio…
Question
100%
EMP_STAFF = "Y" "AS" STAFF PK,FK1 EMP ID ITSTAFF PK,FK1 EMP_ID STAFF_LEVEL STAFF TYPE "IT" ACADSUPPORT PK,FK1 EMP_ID Current Data Historic Data IT_STAFF_GROUP STAFF_TYPE EMPLOYEE PK EMP_ID IT_STAFF_GROUP EMP_LNAME EMP FNAME EMP INITIAL EMP_HIREDATE EMP STAFF EMP ADMIN EMP PROF EMP_PROF="Y" TECHSUPPORT PK,FK1 EMP_ID PROFESSOR PK EMP ID PK,FK1 EMP ID H+ EMP_ADMIN "Y" EMP_JOB_HIST ADMIN PK,FK1 EMP_ID PROF SPECIALTY PROF_RANK "TS" EMPLOYEE EMP_LNAME EMP_FNAME has EMP INITIAL EMP_HIREDATE EMP STAFF EMP_ADMIN EMP PROF OPK ADMIN_POSITION OPK,FK1 PK,FK2 PK Where Employee and Professor table part is extended as shown below Hois dean of HO JOB_HIST PK,FK1 EMP ID PK,FK2 SCHOOL CODE JH_DATE ASSIGN TAKEN JH_DATE_ENDED JH EMP_SALARY EMP_PROF "Y" EMP ID TRN_ID TAKEN DATE PK,FK1 EMP ID TAKEN_TYPE TAKEN_PASS PK PROFESSOR SCHOOL SCHOOL_CODE SCHOOL_NAME FK1 EMP ID PROF_SPECIALTY PROF_RANK SCHOOL JOB_HIST has TRAINING HPK TRN_ID TRN_CATEGORY
expand button
Transcribed Image Text:EMP_STAFF = "Y" "AS" STAFF PK,FK1 EMP ID ITSTAFF PK,FK1 EMP_ID STAFF_LEVEL STAFF TYPE "IT" ACADSUPPORT PK,FK1 EMP_ID Current Data Historic Data IT_STAFF_GROUP STAFF_TYPE EMPLOYEE PK EMP_ID IT_STAFF_GROUP EMP_LNAME EMP FNAME EMP INITIAL EMP_HIREDATE EMP STAFF EMP ADMIN EMP PROF EMP_PROF="Y" TECHSUPPORT PK,FK1 EMP_ID PROFESSOR PK EMP ID PK,FK1 EMP ID H+ EMP_ADMIN "Y" EMP_JOB_HIST ADMIN PK,FK1 EMP_ID PROF SPECIALTY PROF_RANK "TS" EMPLOYEE EMP_LNAME EMP_FNAME has EMP INITIAL EMP_HIREDATE EMP STAFF EMP_ADMIN EMP PROF OPK ADMIN_POSITION OPK,FK1 PK,FK2 PK Where Employee and Professor table part is extended as shown below Hois dean of HO JOB_HIST PK,FK1 EMP ID PK,FK2 SCHOOL CODE JH_DATE ASSIGN TAKEN JH_DATE_ENDED JH EMP_SALARY EMP_PROF "Y" EMP ID TRN_ID TAKEN DATE PK,FK1 EMP ID TAKEN_TYPE TAKEN_PASS PK PROFESSOR SCHOOL SCHOOL_CODE SCHOOL_NAME FK1 EMP ID PROF_SPECIALTY PROF_RANK SCHOOL JOB_HIST has TRAINING HPK TRN_ID TRN_CATEGORY
1. Write a SQL query to list first name, last name and rank of only professors whose rank is Assistant, Associate or full professor (where each rank is coded as ATP, AAP, and FP respectively). 2. Write a MySQL query to show the number of people in each of the professor rank category (which includes adjunct, instructor, visiting professor, assistant professor, associate professor and full professor) and sort the result by the number of people in descending order. 3. Write a MySQL query to list the professor rank where the total number of people the rank is more than 10 people. 4. Write a MySQL query to find names of the IT staff who completed the training (training ID = 5) and the date they passed the training (assuming the date field in the table: TAKEN is of the Date type). 5. Write a MySQL query to find names of those who are both a full professor AND an IT staff who completed at least 2 trainings and the dates they passed the training. 6. Write a MySQL query to find names of those who are either a full professor OR an IT staff who completed at least 2 trainings or both. 7. Write a MySQL query to find those professors who have not served as a School Dean. 8. Write a MySQL query to list all employees by Last name, First name and their EMP admin, EMP staff and EMP Prof status ordered by Last name in alphabetical order. Last name and the three status fields (administrator, staff and professor status) must be displayed all in uppercase letter; first name is displayed with the first letter in uppercase and the rest all in lower case letters.
expand button
Transcribed Image Text:1. Write a SQL query to list first name, last name and rank of only professors whose rank is Assistant, Associate or full professor (where each rank is coded as ATP, AAP, and FP respectively). 2. Write a MySQL query to show the number of people in each of the professor rank category (which includes adjunct, instructor, visiting professor, assistant professor, associate professor and full professor) and sort the result by the number of people in descending order. 3. Write a MySQL query to list the professor rank where the total number of people the rank is more than 10 people. 4. Write a MySQL query to find names of the IT staff who completed the training (training ID = 5) and the date they passed the training (assuming the date field in the table: TAKEN is of the Date type). 5. Write a MySQL query to find names of those who are both a full professor AND an IT staff who completed at least 2 trainings and the dates they passed the training. 6. Write a MySQL query to find names of those who are either a full professor OR an IT staff who completed at least 2 trainings or both. 7. Write a MySQL query to find those professors who have not served as a School Dean. 8. Write a MySQL query to list all employees by Last name, First name and their EMP admin, EMP staff and EMP Prof status ordered by Last name in alphabetical order. Last name and the three status fields (administrator, staff and professor status) must be displayed all in uppercase letter; first name is displayed with the first letter in uppercase and the rest all in lower case letters.
Expert Solution
Check Mark
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
bartleby
This is a popular solution
See solutionCheck out a sample Q&A here
Step 1
VIEW
Step 2
VIEW
bartleby

Trending nowThis is a popular solution!

bartleby

Step by stepSolved in 2 steps

See solution
Check out a sample Q&A here
Blurred answer
Still need help?
Follow-up Questions
Read through expert solutions to related follow-up questions below.
Follow-up Question

Can you Help me with Home work questions 2-8 please.

Solution
Bartleby Expert
by Bartleby Expert
SEE SOLUTION
Follow-up Questions
Read through expert solutions to related follow-up questions below.
Follow-up Question

Can you Help me with Home work questions 2-8 please.

Solution
Bartleby Expert
by Bartleby Expert
SEE SOLUTION
Knowledge Booster
Background pattern image
Computer Science
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
Recommended textbooks for you
Text book image
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Text book image
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Text book image
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
Text book image
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education
SEE MORE TEXTBOOKS