1. Write SQL retrieval commands for each of the following queries:a. Display the course ID and course name for all courses with an MC prefix.b. Display all courses for which Professor Newton has been qualified.c. Display the class roster, including student name, for all students enrolled in section 1003 of MC123 and section 1005 of MC222.2. Write an SQL query to answer the following question: Which instructors are qualified to teach MS211 and MS111?3. Write an SQL query to answer the following question: Is any instructor qualified to teach MS111 and not qualified to teach MS232?4. Write SQL queries to answer the following questions: How many students were enrolled in section 1003 during semester I-2021?5. How many students were enrolled in MS232 during semester I-2021?6. Write an SQL query to answer the following question: Which students were not enrolled in any courses during semester 1-2021? Instructions:• Together with this questionnaire is a list of tables with the corresponding records. Fromthe list create a database and these tables within this database.It is up to you what name you will give to your database. However, you have to use thegiven table names and the columns when you create the tables.Insert the rows to their respective tables.• With this database, answer the following questions/problems.Your answer are the SQL statement that will give the answer to the question/problem andthe result the SQL statement. Write the answer after each of the question or problem.• The SQL statement must use either a join or subquery (whichever is appropriate toaddress the question/problem). Table: STUDENTTable: FACULTYSTUDENTID STUDENTNAMEFACULTYID FACULTYNAMEJohnny BravoPlunger BoyDexter3821462662EinsteinPorscheNewtonPlato1236563636669527725625631Astro Boy72577Table: QUALIFIEDTable: COURSEFACULTYID COURSEIDCOURSEID COURSENAME62662MS111MS111Systems Analysis62662MC123MC123Data Structures77256MS211MS211CompilersNetwork Security77256MC222МC22263636MS111MS232IT Audit63636МC22272577MS21172577MS232Table: ENROLLMENTTable: SECTIONSTUDENTID SECTIONNO SEMESTERSECTIONNO SEMESTER COURSEID3821410001-202110001-2021MS1113821410031-202110011-2021MS1116695210031-202110021-2021MC1236695210051-202110031-2021MC1231236510011-202110041-2021MS2112563110071-202110051-2021МC22210061-2021МC22210071-2021MS232

Actually, SQL stands for Structured Query

. Write SQL retrieval commands for each of the following queries: a. Display the course ID and course name for all courses with an MC prefix. b. Display all courses for which Professor Newton has been qualified. c. Display the class roster, including student name, for all students enrolled in section 1003 of MC123 and section 1005 of MC222.

. Write SQL retrieval commands for each of the following queries: a. Display the course ID and course name for all courses with an MC prefix. b. Display all courses for which Professor Newton has been qualified. c. Display the class roster, including student name, for all students enrolled in section 1003 of MC123 and section 1005 of MC222.

Np Ms Office 365/Excel 2016 I Ntermed

1st Edition

ISBN:9781337508841

Author:Carey

Publisher:Carey

Chapter4: Analyzing And Charting Financial Data

Section: Chapter Questions

Problem 14RA

See similar textbooks

Similar questions

When part of the data in a table depends on a field in the table that isn’t the table’s primary key, it’s known as: transitive dependency partial dependency psychological dependency a foreign key
Insert the following data into your database tables.STUDENTSSTUDENT_ID STUDENT_NAME STUDENT_SURNAMES123456 Neo PetleleS246810 Derek MooreS369121 Pedro NtabaS654321 Thabo JoeS987654 Dominique WoolridgeMODULESMODULE_ID MODULE_NAME MODULE_CREDITDATA6212 Database Intermediate 30INPU221 Desktop Publishing 20PROG6211 Programming 2A 15PROG6212 Programming 2B 15WEDE220 Web Development (Intermediate) STUDENT_MODULESSTUDENT_ID MODULE_IDS123456 PROG6211S123456 PROG6212S246810 DATA6212S369121 DATA6212S369121 INPU221S369121 WEDE220S987654 PROG6211S987654 PROG6212S987654 WEDE220LECTURERSLECTURER_ID LECTURER_NAME LECTURER_SURNAMEL578963 Kwezi MbeteL876592 Julia RobinsL916482 Trevor JanuaryLECTURER_MODULESMODULE_ID LECTURER_IDDATA6212 L578963INPU221 L876592PROG6211 L916482PROG6212 L916482WEDE220 L876592Correct INSERT statements used and all data correctly inserted per table.
* Single tables Display the information (name, position, salary) about a person who earns the highest salary. Display the number of staff grouped by branch. Display each branchID with the number of rental properties grouped by branch. Display each staffID n with the number of rental properties grouped by staff. Display the rental property located Glasgow Display ownerID who owns the rental property 'PA14'. Display the most frequently viewed property. Display available(possible) rental prices Display the name of client who were looks for "House"* Multiple tables Display branch information (city) with the number of rental properties grouped by branch. Display staff information (name, position, branchID, Brach City) with the number of rental properties grouped by staff. Display owner information (name, address, etc.) who owns the rental property 'PL94'. Display the number of rental properties group by the type List staff information who works at London.
Given the following table, COURSES, with the following rows, what values are generated by the query below ? SELECT DISTINCT SUBSTRING(course_code, 1, 4) FROM courses WHERE SUBSTRING(course_code, 1, 1) = 'C' COURSE_CODE COURSE_NAME COMP1027 Introduction to Database Systems COMP2007 Database Systems CCSD1001 Linux Operating System CCSD5002 Java Programming OSSS8027 Cloud Computing ENGL5001 Technical Writing SELECT DISTINCT SUBSTRING(course_code, 1, 4) FROM courses WHERE SUBSTRING(course_code, 1, 1) = 'C' a. CCSD, ENGL b. CCSD, OSSS c. COMP, CCSD d. COMP, ENGL
Any attribute or combination of attributes that can be used to uniquely distinguish one row from another is called a _______________ . Question 41 options: 1) foreign key 2) candidate key 3) secondary key 4) simple key 5) composite key
[ slide-15.png ] Q1. Please write SQL codes to create below rental database table based on the previous slide (slide 15) Video E-R Modling Diagram child table RENTAL 1. checkout_date and dvd_ID coulmns should be combined together as a composite primary key of the rental table, and rest columns are NOT NULL constraints except the checkin_date column (it might hold NULL values). 2. Rental table is a child table, and the member_id column is a foreign key column which references member table’s member_id primary key column. 3. Rental table is a child table, and the dvd_id column is a foreign key column which references dvd table’s dvd_id primary key column. DESC rental Name Null? Type checkout_date NOT NULL DATE dvd_ID NOT NULL NUMBER(4, 0) member_ID NOT NULL NUMBER(4, 0) checkin_date DATE expred_date NOT NULL DATE
QUESTION 2 Hillar Furniture Enterprise is a furniture store owned by Dr. Hillar Addo of AIT. The materials manager of this company maintains a list of suppliers for each of the materials items purchased by the company from outside vendors. The following table shows the essential data required for this application. Hillar Furniture Company Purchasing Data is shown below: Attribute Name Material_ID Material_ Name Unit_of_Measure Standard_Cost Vendor_ID Vendor_name Unit_price Terms_Code Terms Sample Value 3792 Hinges 3” locking Each GHC 5.00 V300 Apex Hardware GHC 4.75 1 COD a) Using the dependency diagram, identify the functional dependencies for this data. You may assume the following: Each material has one or more suppliers. Each supplier may have supply one or more items or may not supply many items. The unit price for a material may vary from one vendor to another The terms of a code for a supplier uniquely identifies the terms of a sale (e.g. code 2…
This is data base question. Schema is also uploaded. 1. Show all the movie names with their type names for those movies which has been released during 2000 to 2009. (Use inner join) 2. Show the customer names along with the movie names for which they booked more than 5 seats. (Use inner join) 3. Show the Name of Staff members and their designation who did some booking for the movie ‘Avengers’. (Use inner join) 4. Show the name of movies which were released in both 2D and 3D. (Use Natural join) 5. Show names of all movies along with the staff names who did booking for them. Show all movie names whether they have been booked by any staff or not. (Use Outer Join) 6. Show all category names along with the movie names that lie in the category. Show all categories whether any movie is there of that category or not. (Use Outer Join) 7. Show all customer names along with movie names they booked. Show all the customer names whether they booked any move or not. Also show all movie names whether…
Table name: Supplier Column Name Role Data Type Length Constraint supplierID Stores the ID of a supplier Number 10 Primary key supplierFName Stores the first name of a supplier Varchar2 30 Cannot be null supplierLName Stores the last Name of a supplier Varchar2 30 Cannot be null supplierDOB Stores the date of birth of a supplier Date - supplierNBPro Stores the number of products supplied by a supplier Number 3 Should be greater than 10 a. Write in SQL a statement that adds a new column named comapnyID of type char(5). This column should be defined as a foreign key that relates the table Supplier to the table Company b. Write in SQL a query that displays the ids of the suppliers who supply the highest number of products. c. Create a view named ‘SupplierDetails’ to hold all the details of suppliers whose last names start with ‘bait’ and who supply between 50 and 200 products.
View 4 Seneca Courses Inc. Customer Forms All customer information for SCI is maintained on file forms. The following are sample customer information forms: CUST_ID 78 CUST _ID 54 NAME YORK UNIVERSITY NAME SENECA COLLEGE LOCATION NORTH YORK LOCATION NORTH YORK SALES_REP ANDRE MARTIN SALES_REP PARVEZ RAI CUST _ID 74 CUST _ID 43 NAME RYERSON NAME INDIGO BOOKS LOCATION TORONTO LOCATION TORONTO SALES_REP PARVEZ RAI SALES_REP ANDRE MARTIN Business Rules: NAME must always be different for each customer. SALES_REP must always have a value. SALES_REP must be an existing employee. A customer only has one sales rep, but the sales_rep may have more than one customer. Normalize the above user view. Document all steps including UNF, 1NF, Dependencies, 2NF, and 3NF and explain or list the…
Problem: JMS TechWizards is a local company that provides technical services to several small businesses in the area. The company currently keeps its technicians and clients’ records on papers. The manager requests you to create a database to store the technician and clients’ information. The following table contains the clients’ information. Client Number Client Name Street City State Postal Code Telephone Number Billed Paid Technician Number AM53 Ashton-Mills 216 Rivard Anderson TX 78077 512-555-4070 $315.50 $255.00 22 AR76 The Artshop 722 Fisher Liberty Corner TX 78080 254-555-0200 $535.00 $565.00 23 BE29 Bert's Supply 5752 Maumee Liberty Corner TX 78080 254-555-2024 $229.50 $0.00 23 DE76 D & E Grocery 464 Linnell Anderson TX 78077 512-555-6050 $485.70 $400.00 29 GR56 Grant Cleaners 737 Allard Kingston TX 78084 512-555-1231 $215.00 $225.00 22…
difference between Primary key; Composite key