DBMS Lab Programs and Queries in SQL
Uploaded by
likhithakonda28DBMS Lab Programs and Queries in SQL
Uploaded by
likhithakonda28Common questions
Powered by AITo ensure data integrity when creating tables in an RDBMS, you can use constraints such as PRIMARY KEY, NOT NULL, and UNIQUE. For example, when creating the Employees table, using a PRIMARY KEY constraint on EmployeeID ensures each row has a unique identifier. The NOT NULL constraint on FirstName and LastName ensures that these columns cannot contain NULL values, and the UNIQUE constraint on Email ensures that all email addresses are distinct .
You can retrieve such a list using the SQL ALL operator in a subquery. The query would look like: SELECT Name FROM Students WHERE Marks > ALL (SELECT Marks FROM Students WHERE Rank > Rank). This uses a subquery to compare a student's marks against each student ranked below them and returns only those students whose marks are greater than all such compared values .
You can use GROUP BY to organize data by product and the HAVING clause to filter groups that meet a certain condition. For example, to find products with total sales revenue exceeding $2000: SELECT ProductName, SUM(Quantity * Price) AS TotalRevenue FROM Sales GROUP BY ProductName HAVING SUM(Quantity * Price) > 2000. GROUP BY aggregates data per ProductName, and HAVING applies a condition only on groups where TotalRevenue exceeds $2000 .
To verify if any students scored more than 80 using EXISTS, you could use: SELECT RollNumber, Name FROM Students s WHERE EXISTS (SELECT 1 FROM Students WHERE Marks > 80 AND RollNumber = s.RollNumber). For NOT EXISTS, you can find students who did not score below 80: SELECT RollNumber, Name FROM Students s WHERE NOT EXISTS (SELECT 1 FROM Students WHERE Marks < 80 AND RollNumber = s.RollNumber).
When using DROP TABLE, consider its irreversible nature and potential data loss, as it deletes the table and all its data permanently. Ensure no dependencies, such as foreign keys or application processes rely on the table. Backup critical data before dropping a table. In a transaction environment, dropping a table may affect other operations, so transactional control is advisable to maintain integrity. Assess impacts thoroughly to prevent unintentional system disruptions .
Creating, querying, and dropping a view involves a sequence of commands. First, use CREATE VIEW to define a view, like example: CREATE VIEW TotalRevenueView AS SELECT ProductName, SUM(Quantity * Price) AS TotalRevenue FROM Sales GROUP BY ProductName. Then, query it like a table: SELECT * FROM TotalRevenueView. Finally, drop it when necessary to free up resources: DROP VIEW TotalRevenueView. This allows for modular use of complex queries .
The INTERSECT operator is directly related to the set theory concept of intersection, which identifies common elements between sets. In SQL, INTERSECT returns distinct rows from queries that appear in both result sets. For example, to find students scoring above 85 who are also ranked in the top 3, you'd use: SELECT Name FROM Students WHERE Marks > 85 INTERSECT SELECT Name FROM Students WHERE Rank <= 3. This provides a subset of students meeting both criteria, demonstrating its utility in filtering shared data points across conditions .
Aggregate functions can evaluate the performance by calculating metrics like total sales, average prices, and number of transactions. For instance, using SUM(Quantity * Price) calculates total revenue, AVG(Price) finds average product price, and COUNT(SaleID) counts total sales transactions. For detailed analysis over a time period, these can be combined with GROUP BY to segment data per product or date, offering insights into sales trends .
The UNION operator is beneficial when you need to merge results from multiple queries into a single result set while eliminating duplicates. It is useful when you want to gather related data from different sources or criteria. For example, combining students scoring above 90 and those ranked in the top 2: SELECT Name, Marks FROM Students WHERE Marks > 90 UNION SELECT Name, Marks FROM Students WHERE Rank <= 2. This avoids the need for separate processing of these data sets .
To add a new column to an existing table, you use the ALTER TABLE command. For example, adding a DateOfJoining column to the Employees table involves: ALTER TABLE Employees ADD DateOfJoining DATE. Subsequently, to update the column values, you use the UPDATE command, such as UPDATE Employees SET DateOfJoining = '2022-01-15' WHERE EmployeeID = 101, to assign specific dates to existing rows .