Q3: Write the following queries in SQL, using the university schem a. classroom(building, room...
Question:
Q3: Write the following queries in SQL, using the university schem
a. classroom(building, room number, capacity) department(dept name, building, budget) course(course id, title, dept name, credits) instructor(ID, name, dept name, salary) section(course id, sec id, semester, year, building, room number, time slot id) teaches(ID, course id, sec id, semester, year) student(ID, name, dept name, tot cred) takes(ID, course id, sec id, semester, year, grade) advisor(s ID, i ID) time slot(time slot id, day, start time, end time) prereq(course id, prereq id)
a. Find the titles of courses in the Comp. Sci. department that have 3 credits.
b. Find the names of all students who have taken at least one Comp. Sci. course; make sure there are no duplicate names in the result
c. Find the maximum enrollment, across all sections, in Autumn 2009.
d. Find the IDs and names of all students who have not taken any course offering before spring 2009
e. Increase the salary of each instructor in the Comp. Sci. department by 10%.
f. Delete all courses that have never been offered (that is, do not occur in the section relation).
g. Insert every student whose tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000.
h. Create a new course 'CS-001', titled 'Weekly Seminar',with 0 credits.
i. Create a section of this course in autumn 2009, with sec id of 1.
j. Enroll every student in the Comp. Sc
i. department in the above section.
SQL:
SQL stands for 'Structured Query Language'. SQL is a language used to create, retrieve and modify data to or from a database. SQL supports a wide range of aggregate functions which can help users perform basic aggregate operations on the data.
Answer and Explanation: 1
Become a Study.com member to unlock this answer! Create your account
View this answera. SELECT title
FROM course
WHERE deptname = 'Comp. Sci.';
b. SELECT s.name
FROM student s
JOIN takes t ON s.ID = t.ID
JOIN course c on...
See full answer below.
Ask a question
Our experts can answer your tough homework and study questions.
Ask a question Ask a questionSearch Answers
Learn more about this topic:
Get access to this video and our entire Q&A library
What is SQL? | Overview & Examples
from
Chapter 9 / Lesson 7
86K
Discover Structured Query Language or SQL. Learn about SQL databases and how SQL programming is used to manage relational databases and facilitate data manipulation.
Related to this Question
- Write the following queries in SQL, using the university schema. classroom(building, room number, capacity) department(dept name, building, budget) course(course id, title, dept name, credits) instruc
- Consider the following database schema for a UNIVERSITY database: -Students (name, major, advisor) -Courses (code, department, name, units) -Schedule (course, term, year, taughtby) -Enrollments (s
- Consider the following enrollment database used at FSU: - Student (FSUID, Name, Major, Level, Age) - Course (CourseName,Department,Time,Venue,FacultyID) - Faculty (FacultyID,Name, Department) - En
- Write single-table SQL queries to answer the following questions or generate the required listings. 1) How many students are enrolled in Section 27 in the first semester of 2017? 2) List the faculty
- Write single-table SQL queries to answer the following questions: 1) How many students are enrolled in Section 2714 in the first semester of 2008? 2) Which faculty members have been qualified to teach
- Consider the following relations: Courses(cnum,title,credits), Professors(pid,pname,dept), Teaches(pid,cnum,semester) (a) How many professors can have the same name? (b) How many departments can a
- Write SQL retrieval commands for each of the following queries: display the class roster, including student name, for all students enrolled in section 2714 of ISM 4212.
- Consider the following relations: Student(ssn, name, address, major) Course (code, title) Registered (ssn, code) ssn and code are underlined a) List the information of the students majoring in 'C
- Consider the following relational schema: Emp (eid, did, sal, hobby) Dept (did, dname, floor, phone) Finance (did, budget, sales, expenses) Consider the following query: SELECT d.dname, f.budget FROM
- Develop a Student Grade Database consisting of the following two tables; STUDENT |Z number (PK) |(Number Field) contains nine digits |Student Name |(Text field -25) | College |(Text field -3) FAU |Ema
- Write a c program for the following question. Design a database to manage the parts in an electrical system. The database should store the following fields: Part Description (string) Part Number (s
- Write SQL queries to answer the question: which students were not enrolled in any courses during semester 1-2008?
- Write a query to display the manager name, department name, department phone number, employee name, customer name, invoice date, and invoice total for the department manager of the employee who made a
- Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course: STUDENT (Ssn, Name, Major, Bdate) COURSE (Course#, Cname, Dep
- Assume a table Emp(ssn, name, salary) of employee records, where ssn is the primary key. The total size of the table is 34,560MB. The table (i.e., the records of the table) is stored in a heap file in
- Consider the following set of requirements for a UNIVERSITY Database that is used to keep track of students' transcripts. (a) The university keeps track of each student's name, student number, social
- A database table consists of the following columns: employee's name, SS#, job-title, hiring-date, years-in-job, base-salary. A bonus calculation program, a personnel promotion application program, and
- Consider the below schema of the university database (keys are in bold and underline): Students(stuID: Integer, stuName: String, gender: String, age: Integer, gpa: Float) Departments(deptName: String,
- Design a class named Employee. The class should keep the following information in the fields: Employee name, Employee number, and Hire Date. Write one or more constructors and the appropriate access
- Design a registrar's database to store information about students, courses, the courses students have taken, and the grades students have gotten in these courses. Courses have a number, a department,
- Please write queries based on the following requirements using Premier Database. -List the transactions by members with the first name starts with "B", including members who have not purchased any ite
- 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)
- 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 S
- Consider the following database. EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode) DEPARTMENT(DNO, TotalSalary, ManagerSSN) STARTING_PAY(JobCode, StartPay) Note that Dept_No in EMPLOYEE table i
- Consider the schema below of the university database: Students(stuID: Integer, stuName: String, gender: String, age: Integer, gpa: Float) Departments(deptName: String, numPhDs: Integer) ProfessorWorks
- 1. Consider the following database. EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode) DEPARTMENT(DNO, TotalSalary, ManagerSSN) STARTING_PAY(JobCode, StartPay) Note that Dept_No in EMPLOYEE
- In Java, Create a class named CollegeCourse that includes 4 fields ? the department name (e.g. ?ENG?), the course number (e.g. 101), the number of credits (e.g. 3) and the course fee (e.g. ?360?). Add
- The following tables form part of a database held in a relational DBMS:- Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest
- Create a class that holds data about a job applicant. Include a name, a phone number, and four Boolean fields that represent whether the applicant is skilled in each of the following areas: word pro
- Create a class that holds data about a job applicant. Include a name, a phone number, and four Boolean fields that represent whether the applicant is skilled in each of the following areas: word proce
- Write SQL SELECT commands to answer the following queries. Consider the following relational database for the Super Baseball League. It keeps track of teams in the league, coaches and players on the t
- Write code to create a view in SQL SERVER named ITEM_ORDER consisting of the fields item number, description, price from table Itemmaster and order_number, order_date, item number, number_ordered, and
- 7. A database at a college is required to support the following requirements. Complete the information-level design for this set of requirements. Use your own experience to determine any constraints y
- Please write queries based on the following requirements using Premier Database.. -List all the full names of the employees who sold item 000078. Show your results in ascending order on employee's l
- Consider the following database. EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode) DEPARTMENT(DNO, TotalSalary, ManagerSSN) STARTING_PAY(JobCode, StartPay) Note that Dept_No in EMPLOYEE tab
- 1. Write SQL SELECT commands to answer the following queries. Consider the following relational database for the Super Baseball League. It keeps track of teams in the league, coaches and players on th
- Write an SQL statement to display the OwnerLastName, OwnerFirstName, PetName, PetType, PetBreed, and AverageLifeExpectancy for pets with a known PetBreed. Use PET_3. The PET_OWNER, PET_3, and BREED ta
- Design a DNS namespace for your organization that conforms to the following guidelines. The root domain name for the organization is adatum.com. All of the additional domains you create must be subor
- SQL DBMS Administration Use Notepad to create the commands that will run the following queries/problem scenarios. 1. Create a view named LARGE_SLIP. It consists of the marina number, slip number, re
- Consider the table: STUDENT (StudentNumber, StudentName, Dorm, RoomType, DormCost, Sibling, Nickname) Assume that students pay different dorm costs, depending on the type of room they have, but that a
- PROGRAMMING CHALLENGES: 1. Employee and ProductionWorker Classes: a. Design a class named "Employee." The class should keep the following information in fields: *Employee name *Employee number in the
- Staff (staffNo, name, dept, skillCode) Skill (skillCode, description, chargeOutRate) Project (projectNo, startDate,endDate,budget, projectManagerStaffNo) Booking (staffNo, projectNo, dateWorkedOn, tim
- The following is the informal record structure of my home address: Name Mani M. Subramanian Address 1652 Harts Mill Road City Atlanta State GA Zip Code 30319 Write for your record: (a) the informal re
- Add the following row to the SALES_REP table: rep number: 35, last name: Lim; first name: Louise; street: 535 Vincent Dr.; city: Grove; state: CA; postal code: 90092; com- mission: 0.00; and rate: 0.0
- Get the AdventureWorks Data Dictionary from technet.microsoft.com/en-us/library/ms124438(v=sql.100).aspx, and then write DML SQL queries to answer the following questions. 1. Use the SELECT statement
- A student-instructor-course database has the following schema: S(S#,SNAME,MAJOR,CITY) I(I#,INAME,DEPT,CITY) C(C#,CNAME,UNITS) E(S#,C#,GRADE) O(I#,C#,TIME,ROOM#) Use relational algebra expressions to a
- Use Notepad to create the commands that will run the following queries/problem scenarios. 1. List the owner number, last name, and first name of every boat owner. 2. List the complete MARINA table.
- Write a database description for each of the relations shown using SQL DDL. Assume the following attribute data types: StudentID (integer, primary key) StudentName (25 characters) FacultyID (integer,
- Give an expression in relational algebra for each of the following queries: a. Find all loan numbers with a loan value greater than $50,000. b. Find the names of all depositors who have an account wit
- Write SQL queries for the books database that perform each of the following tasks: a) Select all authors from the Authors table with the columns in the order lastName, firstName and authorID. b) Selec
- Create a program to handle a college class grades: Capture the Teacher's name Capture the Class designation The program should ask how many students are in the class and do the following for each s
- A student has established the following monthly budget: Budget Categories Budgeted amount Housing $ 580.00 Utilities $ 150.00 Household Expen
- The next three questions ask you to trace a sort in Java. To trace a sort, write the contents of the array after each pass of the sort. Write each number separated by a space. Trace a selection sort o
- Write a program that uses a structure to store the following data: Member Name Description Name Student Name (string) IdNum Student ID Number (int) Tests Pointer to an array of test scores (dou
- Create a program that manages a text file containing student records. Each record will contain a student name and 3 exam scores. Each field in a record is separated by a comma (no space), and the name
- The following is an example of a grade report for a student at TAMUCT. In order to create this grade report, you have been hired to build a data model. 1. Identify entity types. 2. Identify relation
- Write a menu based program to maintain student records. Your program should take the following inputs: 1. Student first name (max. 20 characters) 2. Student last name, (max. 20 characters) 3. Stud
- Write an application that retrieves a student name and three scores per line from a text file. Process the values by calculating the average of the scores per student. Write the name and average to a
- 1. A common restriction placed on table and column names by DBMSs is that names can contain only letters, numbers, and what else? 2. Which SQL command do you use to create a table by describing its la
- Devise a title and present a description for a potential project in which you aim to design a computer bases information system for a socio-economic unit. In addition, specify what is the information technology that you intend to use in order to design, d
- 1. Create a cross-join that displays the last name and department name from the employees and departments tables. 2. What is the result of the query that you have used for question 1? 3. Create a qu
- Given the following relational schemas: EMPLOYEE(SSN, NAME, SEX, DNUMBER) DEPARTMENT(DNUMBER, DLOCATION) DLOCATION(DNUMBER, DLOCATION) PROJECT(PNUMBER, PNAME, PLOCATION) WORKSON(SSN, PNUMBER, HOURS) 1
- Which of the following is not true about changing field names in a query? A) Field names in aggregate queries are a composite of the selected aggregate function and the table field name. B) The field
- Lab: 1. Using the AdventureWorks database, query "Sales.SalesOrderDetail" table to find out the total number of parts associated with each of the following SalesOrderIDs: 43660, 43670, and 43672. Thi
- Write a SQL query to return the average rating per genre, saved in a table called ?query2? which has two attributes: name, rating. The database is as follows:
- SQL: This Critical Thinking Assignment requires you to use the SQL Server instance named SQLSERVERCSU and the HandsOnOne database and tables that you created in Modules 1 and 3, respectively. The obje
- Write using a Python program. Write a class named Employee that holds the following data about an employee in attributes: name, ID number, department and job title. All data attributes should be priva
- Write a menu based program to maintain student records. Your program should take the following inputs: 1. Student first name (max. 20 characters) 2. Student last name, (max. 20 characters) 3. Stude
- Write a menu based C program to maintain student records. Your program should take the following inputs: 1. Student first name (max. 20 characters) 2. Student last name, (max. 20 characters) 3.
- Relational Schema: Book(ISBN:String, Title:String, Author:String, Category:String, Year:Integer) Student(Student_Id:Integer, First_Name:String, Last_Name:String, Major:String, Gender:Char, DOB:Date)
- Here's an example of SQL Query. Question: What is the title of the book with the book code 0200? The SQL Query For the Same is: SELECT TITLE FROM BOOK WHERE BOOK_CODE="0200"; Answer: The Stra
- Create a program to handle college class grades: Capture the Teacher's name Capture the Class designation The program should ask how many students are in the class and do the following for each stu
- Write a database description for each of the relations shown using SQL DDL. Assume the following attribute data types: StudentID (integer, primary key) StudentName (25 characters) FacultyID (integ
- SQL coding help Query 1: Which guest(s) (only list the guestNo) have stayed in both hotelNo 2 and hotelNo 4? Query 2: which hotels (only list the hotel number) do not have family rooms? Query 3: Wh
- (Python) Create a comment block with the following information: Your Name Course Name, Section (example: ENTD200 B002 Spr15) Instructor name Week # Date completed Problem 1: Write a program that will
- You have been asked by the Payroll department to create a program that will calculate the weekly pay for the company's 25 employees. The employee names and pay will be stored in arrays. The program
- Write a menu based C program to maintain student records. Your program should take the following inputs: 1. Student first name (max. 20 characters) 2. Student last name, (max. 20 characters) 3. Stu
- Use C# for the following. A university has the following dormitories: Allen Hall $1,500 per semester Pike Hall $1,600 per semester Farthing Hall $1,800 per semester University Suites $2,500 per semest
- You will design and build a small SQL database for a Human Resources (HR) Department. The HR Department wants to keep track of their employees, the skills they possess, the specific job positions that
- 1. Write a CREATE VIEW statement that defines a view named InvoiceBasic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the
- Fill in the blank. To obtain information from a database, a programmer or database administrator would use a __________.
- Write a single SQL command to increase all price rental fee values by $0.50.
- You are given a file consisting of students' names in the following form: lastName, firstName middleName. (Note that some students may not have a middle name.) Write a program that converts each name
- You are given a file consisting of students' names in the following form: lastName firstName middleName. (Note that a student may not have a middle name.) Write a program that converts each name to th
- Consider the bank database. branch(branch name, branch city, assets) customer (customer name, customer street, customer city) loan (loan number, branch name, amount) borrower (customer name, loan num
- Assume that there is a file named scores in the following format: Each row of the file has first name, last name followed by the score. Assume that both first name and last names are single words, and
- You are creating a table called Department with fields for the primary key DeptID (Integer) and department name (VARCHAR). What (if anything) is wrong with the following code? CREATE TABLE Department
- Create an Employee class. Items to include as data members are employee number, name, date of hire, job description, department, and monthly salary. The class is often used to display an alphabetical
- In MATLAB, create a file that has some college department names and enrollments. (For example, it might look like this: Aerospace 201 Mechanical 66) Write a script that will read the information from
- Use C structs to create and manage an MP3 song database. An MP3 song has the following information about it: a. Title of the song - A string of at most 40 characters b. Artist names - A maximum of thr
- Implement a base class named Person. Derive the classes Student and Professor from Person. Every Person has a name and birthdate. Every Student has a major, and every Professor has an income. Write th
- Code an SQL statement to create a view named DogBreedCountView that shows each PetBreed of dog and the number of each PetBreed in the database: PetID PetName PetType PetBreed PetDOB PetWeight OwnerID
- The A12.txt file below has 2 columns, with the first column being the name of the employee and the second column holding their salary. Read the data into a 2-dimensional array of size 1,000 rows. Once
- Write a program that will read a series of names of people from a data file that has been created with extra blank spaces and reformat the names into a standardized format. The data file is mp5names.
- Provide an easy to access to a listing of the number of credits needed to be considered Freshman, Sophmore, Junior or Senior in college. Prompt the user to input the class level and print out the mini
- Write a C++ code that will read student's names and test scores from a file, and output each student's name, test score, and relevant letter grade. It will also print out the names of each student who
- Write a program that reads form the user the grades of 10 students in an exam of 3 questions. It stores the grades in a 2D array. Then it finds the following: 1. Display the 2D array of all grades alo
- C++ assignment 1. Employee and ProductionWorker ClassesDesign a class named Employee. The class should keep the following information in Employee name Employee number Hire date (A class called 'date
- Create a menu based program to maintain student records. Your program should take the following inputs: 1. Student first name (max. 20 characters) 2. Student last name, (max. 20 characters) 3. Stu
- Write a SELECT statement that returns a single value that represents the sum of the largest unpaid invoices submitted by each vendor. Use a derived table that returns MAX(InvoiceTotal) grouped by Vend
Explore our homework questions and answers library
Browse
by subject