SQL Queries for Orders and Faculty Data
Uploaded by
consuelo.ricky346SQL Queries for Orders and Faculty Data
Uploaded by
consuelo.ricky346Common questions
Powered by AIThe output of the query SELECT FID, MIN(Fees), MAX(Fees) FROM COURSES GROUP BY FID will show each unique FID paired with the minimum and maximum fees found in groups of courses sharing that FID. Grouping modifies aggregate functions to operate within these defined groups rather than across the entire table .
To display the total quantity for each product excluding those with a total quantity less than 5, you can use the SQL query: SELECT Product, SUM(Quantity) as TotalQuantity FROM ORDERS GROUP BY Product HAVING TotalQuantity >= 5. This query groups the table by Product, calculates the sum of Quantity for each group, and filters those groups where the sum is 5 or more .
To delete all orders where the Product is 'Laptop', use the command: DELETE FROM ORDERS WHERE Product='Laptop'. Specifying conditions when deleting data is crucial because it ensures that only the intended records are removed, preventing the accidental deletion of unrelated or important data .
To calculate the sum of prices where the quantity is null, use the SQL query: SELECT SUM(Price) FROM ORDERS WHERE Quantity IS NULL. The IS NULL condition checks for rows where the Quantity is null, and SUM aggregates the Price of these rows .
To display names of faculty teaching 'System Design', use the query: SELECT FName, LName FROM FACULTY F JOIN COURSES C ON F.FID=C.FID WHERE C.CourseName='System Design'. Joins are fundamental in SQL because they enable the combination of related data from multiple tables into a cohesive result set .
To retrieve distinct customer names from the 'ORDERS' table, use the query: SELECT DISTINCT C_Name FROM ORDERS. The DISTINCT keyword ensures that duplicate values are removed from the query results, allowing only unique entries to be displayed .
To display the 'ORDERS' table sorted by total price in descending order, use the query: SELECT * FROM ORDERS ORDER BY Price DESC. Sorting in SQL is achieved with the ORDER BY clause, which sorts the result set by one or more columns. By default, sorting is in ascending order; specifying DESC reverses this to descending order .
The Cartesian Product of two tables is obtained via the SQL query: SELECT * FROM FACULTY, COURSES, without a WHERE clause. This operation combines every row from the first table with every row from the second, resulting in a potentially very large result set. While useful for scenarios such as combinatorial analysis, it can be problematic due to its high computational cost and the generation of irrelevant results .
Saman can display course details with fees in the range of 20000 to 50000 using the query: SELECT * FROM COURSES WHERE Fees BETWEEN 20000 AND 50000. The BETWEEN operator is used for filtering within a specified range, including both boundary values .
To increase course fees by 500 for courses with 'Computer' in their names, use the query: UPDATE COURSES SET Fees = Fees + 500 WHERE CourseName LIKE '%Computer%'. This approach uses the LIKE operator to match patterns within strings, allowing targeted updates based on partial matches .