Cia 1 Part B
Cia 1 Part B
Cia 1 Part B
courses taught by Professor Smith using relational algebra, you can use the following
expression:
Copy code
This combines the Professors and Courses tables based on the common attribute professor_id.
This further combines the result from step 1 with the Enrollments table based on the common
attribute course_id.
This combines the result from step 2 with the Students table based on the common attribute
student_id.
This filters the rows based on the condition that the major is 'Computer Science' and the
professor_name is 'Professor Smith'.
2) To retrieve the names of employees who are assigned to projects in the "Software Development"
department using relational algebra, you can use the following expression:
arduino
Copy code
This combines the Departments and Projects tables based on the common attribute department_id.
This further combines the result from step 1 with the Assignments and Employees tables based on
the common attributes project_id and employee_id.
This filters the rows based on the condition that the department_name is 'Software Development'.
This expression ensures that you get the names of employees who are assigned to projects in the
"Software Development" department.
3) Sure, let's design a set of normalized tables for the online bookstore database. We'll start with an
initial set of tables and then go through the normalization steps:
Authors Table:
AuthorName
Biography
Books Table:
Title
Genre
Price
Customers Table:
Name
ShippingAddress
Orders Table:
OrderDate
TotalPrice
OrderDetails Table:
Quantity
No repeating groups.
No partial dependencies.
In the Orders table, we have the composite primary key {OrderID, ISBN}, and both fields are
necessary to uniquely identify a record.
No transitive dependencies.
AuthorGenres Table:
Genre
Remove Genre from the Books table and link it to the AuthorGenres table through AuthorID.
Title
Price
Now, you can join the Books and AuthorGenres tables based on the AuthorID to retrieve the genre
information for each book. This ensures that there are no transitive dependencies, and the database
is in 3NF.
4) Certainly! Let's design a set of normalized tables for the student, courses, instructors,
departments, and enrollments in a university database. We'll follow the normalization steps to
ensure the database is up to at least third normal form (3NF):
Students Table:
FirstName
LastName
Address
Departments Table:
DepartmentName
Instructors Table:
FirstName
LastName
Email
Courses Table:
CourseName
Enrollments Table:
EnrollmentDate
Grade
No repeating groups.
No partial dependencies.
In the Enrollments table, we have the composite primary key {StudentID, CourseID}, and both fields
are necessary to uniquely identify a record.
InstructorDepartments Table:
Remove DepartmentID from the Instructors table and link it to the InstructorDepartments table
through InstructorID.
Instructors Table:
FirstName
LastName
Now, you can join the Instructors and InstructorDepartments tables based on the InstructorID to
retrieve the department information for each instructor. This ensures that there are no transitive
dependencies, and the database is in 3NF.
5) Certainly! Below are the DDL statements for creating the database schema for student enrollment,
courses, professors, and departments. I'll use MySQL syntax for illustration:
sql
Copy code
);
DepartmentID INT,
);
DepartmentID INT,
ProfessorID INT,
);
);
-- Create Enrollments Table
StudentID INT,
CourseID INT,
);
In this schema:
The Professors table stores information about professors, including their department.
The Courses table stores information about courses, including the department offering the course
and the professor teaching it.
The Enrollments table represents the many-to-many relationship between students and courses.
Each record in this table signifies a student enrolled in a specific course.
Please note that this is a basic schema, and you might need to adjust the data types, constraints, and
additional fields based on specific requirements or additional information needed in your
application.
6) Certainly! Below are DDL statements to create a simple database schema for managing inventory,
orders, customers, and suppliers. I'll use MySQL syntax for illustration:
sql
Copy code
ContactPerson VARCHAR(255),
PhoneNumber VARCHAR(20),
);
);
ProductID INT,
SupplierID INT,
);
PhoneNumber VARCHAR(20)
);
CustomerID INT,
);
OrderID INT,
ProductID INT,
);
In this schema:
The ProductSuppliers table represents the many-to-many relationship between products and
suppliers.
The OrderDetails table represents the many-to-many relationship between orders and products.
This schema allows for efficient tracking of inventory, supplier relationships, customer orders, and
the details of each order. Adjustments may be needed based on specific business requirements and
additional details you might want to include in your application.
7) Certainly! Below are SQL queries for each of the tasks you mentioned:
sql
Copy code
sql
Copy code
UPDATE Products
This query updates the price of a product with the specified product_id in the Products table.
sql
Copy code
FROM Customer
This query retrieves the total number of orders placed by each customer by joining the Customer and
Orders tables and using the COUNT aggregate function.
sql
Copy code
This query deletes a product record with the specified product_id from the Products table. Please be
cautious while using the DELETE statement as it permanently removes data.
Make sure to replace the sample data and column names with your actual database schema and
data.
FROM Appointments;
FROM Doctors
GROUP BY Specialization;
FROM Doctors d
LIMIT 1;Calculate the total number of appointments scheduled for each day:
FROM Appointments
GROUP BY AppointmentDate;
9) Retrieve the names of all products that are currently out of stock.
sql
Copy code
SELECT name
FROM Products
WHERE stock_quantity = 0;
Find the names of all products that have been ordered at least once.
sql
Copy code
FROM Products p
sql
Copy code
SELECT name
FROM Products
Retrieve the names of products that have been ordered by customers with a specific customer ID.
sql
Copy code
FROM Products p
Replace [specific_customer_id] with the actual customer ID you want to query for in the fourth task.
10) Retrieve the names of students who have scored higher than the average grade in the subject
'Mathematics':
sql
Copy code
SELECT s.Name
FROM Students s
AND g.Grade > (SELECT AVG(Grade) FROM Grades WHERE Subject = 'Mathematics');
Retrieve the names of students who are older than the average age of all students:
sql
Copy code
SELECT Name
FROM Students
sql
Copy code
SELECT Subject
FROM Grades
GROUP BY Subject
Retrieve the subjects in which at least one student has scored higher than the average grade:
sql
Copy code
FROM Grades
These queries should help you accomplish the specified tasks with your database schema.
11) Here are the SQL queries for each of the tasks:
Calculate the total number of orders placed:
sql
Copy code
FROM Orders;
sql
Copy code
FROM Order_Details od
sql
Copy code
FROM Products;
sql
Copy code
FROM Products;
These queries should provide the desired information based on the provided database schema.
12) Retrieve the names of employees who are earning more than the average salary of all employees
in their respective departments:
sql
Copy code
SELECT e.Name
FROM Employees e
INNER JOIN (
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
Retrieve the names of employees who are earning more than the highest salary in their respective
departments:
sql
Copy code
SELECT e.Name
FROM Employees e
INNER JOIN (
FROM Employees
GROUP BY DepartmentID
Retrieve the names of employees who are earning more than the average salary of employees in the
same department with a higher employee ID:
sql
Copy code
SELECT e1.Name
FROM Employees e1
INNER JOIN (
FROM Employees
GROUP BY DepartmentID
sql
Copy code
SELECT e1.Name
FROM Employees e1
INNER JOIN (
FROM Employees
GROUP BY DepartmentID
sql
Copy code
SELECT name
FROM Students
Find the names of all courses offered by the Computer Science department:
sql
Copy code
SELECT name
FROM Courses
List the names of students who are enrolled in at least one course offered by the Computer Science
department:
sql
Copy code
FROM Students s
JOIN Enrollments e ON s.student_id = e.student_id
Retrieve the names of all students who are not enrolled in any course:
sql
Copy code
SELECT name
FROM Students
14) Retrieve the names of employees who have the highest salary in each department for the year
2023:
sql
Copy code
FROM Employees e
FROM Salaries
GROUP BY DepartmentID
);
Retrieve the department names along with the total salary expenditure for each department in the
year 2023:
sql
Copy code
FROM Departments d
JOIN Employees e ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentName;
Retrieve the names of employees who have changed departments at least once:
sql
Copy code
FROM Employees e
GROUP BY e.Name
Retrieve the names of employees who have earned a salary higher than the average salary of all
employees in the year 2023:
sql
Copy code
FROM Employees e
SELECT AVG(Salary)
FROM Salaries
);
These queries should give you the desired results based on the given schema and requirements.
sql
Copy code
sql
Copy code
FROM Customers c
GROUP BY c.name;
List the names of customers who have placed more than one order:
sql
Copy code
SELECT c.name
FROM Customers c
GROUP BY c.name
Retrieve the names of customers who have placed an order after a specific date (let's assume the
specific date is 'YYYY-MM-DD'):
sql
Copy code
FROM Customers c
16)Certainly! Below are SQL queries for each of the tasks you mentioned:
sql
Copy code
SELECT DISTINCT Products.name
FROM Customers
This query retrieves the names of all products ordered by a specific customer by joining the relevant
tables.
sql
Copy code
FROM Products
GROUP BY Products.name;
This query calculates the total number of orders for a specific product by joining the relevant tables
and using the COUNT aggregate function.
sql
Copy code
FROM Products
GROUP BY Products.name;
This query calculates the total revenue generated from a specific product by multiplying the price
with the quantity and using the SUM aggregate function.
List the names of customers who have never placed an order:
sql
Copy code
SELECT Customers.name
FROM Customers
This query retrieves the names of customers who have never placed an order by using a LEFT JOIN
and checking for NULL values in the Orders table.
Make sure to replace the sample data and column names with your actual database schema and
data.
17) Certainly! Below are SQL queries for each of the tasks you mentioned:
Retrieve the names of customers who have placed orders for products that are priced higher than
the average price of all products:
sql
Copy code
FROM Customers C
This query retrieves the names of customers who have placed orders for products priced higher than
the average price of all products.
Retrieve the names of customers who have placed orders for more than one product:
sql
Copy code
SELECT C.Name
FROM Customers C
This query retrieves the names of customers who have placed orders for more than one product by
using the COUNT function and GROUP BY clause.
Retrieve the names of customers who have placed orders for products with a total quantity greater
than the average quantity of all products:
sql
Copy code
SELECT C.Name
FROM Customers C
Retrieve the names of customers who have placed orders for all available products:
sql
Copy code
SELECT C.Name
FROM Customers C
SELECT P.ProductID
FROM Products P
SELECT O.ProductID
FROM Orders O
);
This query retrieves the names of customers who have placed orders for all available products by
using the NOT EXISTS clause.
Make sure to replace the sample data and column names with your actual database schema and
data.
18) ertainly! Below are SQL queries for each of the tasks you mentioned:
Retrieve the titles of books along with the names of authors for books that are currently available:
sql
Copy code
FROM Books
This query retrieves the titles of books along with the names of authors for books that are currently
available by joining the Books, BookAuthors, and Authors tables.
Retrieve the titles of books along with the names of authors for books with ISBN starting with '978':
sql
Copy code
FROM Books
Retrieve the titles of books along with the names of authors for books written by authors whose
names start with 'J':
sql
Copy code
FROM Books
This query retrieves the titles of books along with the names of authors for books written by authors
whose names start with 'J' using the LIKE operator.
Retrieve the titles of books along with the names of authors for books published after the year 2010:
sql
Copy code
FROM Books
This query retrieves the titles of books along with the names of authors for books published after the
year 2010 by using the YEAR function on the PublicationDate column.
Make sure to replace the sample column names with your actual database schema and data.
19) Certainly! Below are SQL queries for each of the tasks you mentioned:
Copy code
SELECT Courses.name
FROM Students
WHERE Students.name = 'John Doe'; -- Replace 'John Doe' with the specific student name
This query retrieves the names of all courses taken by a specific student by joining the Students,
Enrollments, and Courses tables.
sql
Copy code
FROM Students
GROUP BY Students.name;
This query calculates the total number of courses taken by each student by using the COUNT
aggregate function and joining the Students and Enrollments tables.
sql
Copy code
FROM Courses
GROUP BY Courses.department;
This query calculates the total number of students enrolled in each department by using the COUNT
aggregate function and joining the Courses, Enrollments, and Students tables.
Retrieve the names of students who are not enrolled in any course:
sql
Copy code
SELECT Students.name
FROM Students
This query retrieves the names of students who are not enrolled in any course by using a LEFT JOIN
and checking for NULL values in the Enrollments table.
Make sure to replace the sample data and column names with your actual database schema and
data.
20) Retrieve the names of customers along with the total amount spent by each customer:
FROM Customers c
Retrieve the product names along with the total quantity sold for each product:
FROM Products p
Retrieve the product names along with the total amount earned for each product:
FROM Products p
Retrieve the names of customers along with the products they have ordered, ordered in descending
order of order dates:
FROM Customers c