Assume the following table- Degree (degcode, name, subject) Candidate (seatno, degcode, name, semester, month, year, result) Marks (seatno, degcode, semester, month, year, papcode, marks) [degcode - degree code, name - name of the degree (Eg. BSc.), subject - subject of the course (Eg. DBMS), papcode - paper code (Eg. CSE-3105)] Solve the following queries using SQL; iii. All the degree codes which are there in the candidate table but not present in degree table in the order of degcode. iv. The name of all the candidates who have got less than 40 marks in exactly 2 subjects

Assume the following table- Degree (degcode, name, subject) Candidate (seatno, degcode, name, semester, month, year, result) Marks (seatno, degcode, semester, month, year, papcode, marks) [degcode - degree code, name - name of the degree (Eg. BSc.), subject - subject of the course (Eg. DBMS), papcode - paper code (Eg. CSE-3105)] Solve the following queries using SQL; iii. All the degree codes which are there in the candidate table but not present in degree table in the order of degcode. iv. The name of all the candidates who have got less than 40 marks in exactly 2 subjects

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Q: a) List all employees. Relational Algebra(RA): Tuple Relational Calculus(TRC): Domain Relational…

A: Actually, given list of employees Relational Algebra(RA): Tuple Relational Calculus(TRC): Domain…

Q: N:M relationships a.) require that the primary key from each table is deleted. b.) require that a…

A: Here is the solution;

Q: You have been employed as a Database Developer, and you have been given the choice to utilize either…

A: Database Developer: A Database Developer, also known as a Database Programmer, is in charge of…

Q: Considering the following ER diagram, list the relational šchema. Indlcate the primaly and foreign…

A: Schema Approach: 1- I have created this schema design in MySQL workbench. 2- MySQL workbench is a…

Q: You have been employed as a Database Developer, and you have been given the choice to utilize either…

A: Given that: A trigger is a sort of stored procedure in a database that is automatically invoked when…

Q: is the number of attributes/fields in a relation. a. Degree b. Cardinality O c. Tuple d. Row

A: Degree: In DBMS, degree is the number of attributes or column present in the table Cardinality: In…

Q: The following tables form part of a database held in a relational DBMS: Hotel (hotelNo, hotelName,…

A: Please refer to the following step for the complete solution to the problem above.

Q: Construct appropriate relational schemes for each of the E-R diagrams below.

A: paper(title, author) cite(title) Explanation: The attribute "title" is the primary key for the…

Q: QUESTION 3 The following tables form part of a database held in a relational DBMS. Hotel (hotelNo,…

A: a) The SQL query for the given problem is as follows: use demo;CREATE TABLE Hotel (hotelNo int Not…

Q: The following tables form part of a database held in a relational DBMS: Hotel…

A: 1. The question is asking about a database schema consisting of several tables in a relational…

Q: Task 4: As a part of marketing strategy, the Marketing team continuously conducting an advertising…

A: Actually, database is a used to stores the data.

Concept explainers

Procedures in SQL

A pre-compiled stored procedure is a logical unit made up of one/more SQL statements (SQL stands for Structured Query Language). The database server saves it as an object. An SQL procedure is a collection of SQL statements and logic that is written and saved to accomplish a specified goal. An SQL procedure consists of a name, a parameter list, and a set of Transact-SQL statements. Stored procedures are stored as named objects in SQL Database Server.

Question

Assume the following table-
Degree (degcode, name, subject)
Candidate (seatno, degcode, name, semester, month, year, result)
Marks (seatno, degcode, semester, month, year, papcode, marks)
[degcode - degree code, name - name of the degree (Eg. BSc.), subject - subject of the course
(Eg. DBMS), papcode - paper code (Eg. CSE-3105)]
Solve the following queries using SQL;
iii. All the degree codes which are there in the candidate table but not present in degree table
in the order of degcode.
iv. The name of all the candidates who have got less than 40 marks in exactly 2 subjects

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

This is a popular solution!

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 2 steps with 2 images

SEE SOLUTION Check out a sample Q&A here

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

Convert the following record to a normalized data structure up to 3NF showing all attributes identifying primary keys and foreign keys. Grade-Report Number Name Address Number Title Name Room Grade 38214 Bright 123 Smith St IS350 Database CODD B104 A 38214 Bright 123 Smith St IS465 Sys Anal KEMP B213 C 69173 Smith 35 Main Rd IS465 Sys Anal KEMP B213 A 69173 Smith 35 Main Rd PM300 Proj Mgt LEWIS D317 B 69173 Smith 35 Main Rd QM440 OpSys KEMP B213 A
1. Write a database description for each of the relations shown, using SQL DDL (shorten, abbreviate, or change any data names, as needed for your SQL version). Assume the following attribute data types: StudentID (integer, primary key) StudentName (25 characters) FacultylD (integer, primary key) FacultyName (25 characters) CourselD (8 characters, primary key)
Using the database schema attached, write SQL query for these statements: (1) List stores with more than 200 customers. (2) Display the average amount paid by each customer, along with the customer's first and last name.
The database should contain the following six tables: Artists (artist_id: char(30), artist_name: char(30), artist_pop: int(5))Tracks (track_id: char(30), track_name: char(30), duration: int(10), tempo:real) Record (artist_id: char(30), track_id: char(30))Users (user_id: char(30), user_name: char(30), age: int(5), nationality: char(30), num_track_listened: int(10))Listen (user_id: char(30), track_id: char(30))Follow (user_id: char(30), artist_id: char(30), follow_date: datetime) Write a single SQL query for each request below. Find the ids of those users who have listened to at least one track but have not followed any artists. Find the ids and names of those users who have listened to some tracks recorded by the artist named 'Adele'. Find the ids and names of those users who have not listened to any track recorded by the artist named 'Adele'. Use ''NOT EXISTS'' to answer this query.
Design a survey form of your own. Fill it out as it might have been completed during the database design for Henry Books. For any questions you have too little information to answer, make a reasonable guess. (Ray Henry, the owner of a bookstore chain named Henry Books, gathers and organizes information about branches, publishers, authors, and books. Each local branch of the bookstore has a number that uniquely identifies the branch. In addition, Ray tracks the branch’s name, location, and number of employees. Each publisher has a code that uniquely identifies the publisher. In addition, Ray tracks the publisher’s name and city. The only user of the Book database is Ray, but you do not want to treat the entire project as a single user view. Ray has provided you with all the reports the system must produce, and you will treat each report as a user view. Ray has given you the following requirements: User View 1 Requirements: For each publisher, list the publisher code, publisher name, and…
What are functional dependencies, and how do they relate to normalization?
Assume the following table- Degree (degcode, name, subject) Candidate (seatno, degcode, name, semester, month, year, result) Marks (seatno, degcode, semester, month, year, papcode, marks) [degcode - degree code, name - name of the degree (Eg. BSc.), subject - subject of the course (Eg. DBMS), papcode - paper code (Eg. CSE-3105)] Solve the following queries using SQL; iii. All the degree codes which are there in the candidate table but not present in degree table in the order of degcode. iv. The name of all the candidates who have got less than 40 marks in exactly 2 subjects