SQL Practical Questions
Uploaded by
Asha ChannappaSQL Practical Questions
Uploaded by
Asha ChannappaCommon questions
Powered by AIFirst, establish a 'Student' table with relevant academic and demographic fields. To filter students in specific programs like 'BASC' or 'CEBA', use a query: 'SELECT * FROM Student WHERE Combn IN ('BASC', 'CEBA')'. For gender distribution, employ a group by with a count function: 'SELECT Gender, COUNT(*) FROM Student GROUP BY Gender' . This approach aids in evaluating the combination preferences and gender-based enrollment statistics.
First, create a table named 'marks' with attributes such as Rollno, Sname, and various subject marks fields, ensuring constraints like 'Not null' and value range between 0 to 100 where applicable . After inserting records, use SQL 'ALTER TABLE' to add 'Total' and 'Percentage' attributes. To calculate total marks, use: 'UPDATE marks SET Total = Lang_mrks + Eng_mrks + Sub1_mrks + Sub2_mrks + Sub3_mrks + Sub4_mrks'. For percentage: 'UPDATE marks SET Percentage = (Total/600)*100'. Finally, order the records by percentage using 'SELECT * FROM marks ORDER BY Percentage DESC' .
SQL optimizes utility billing with conditional computations. By creating a 'BESCOM' table and computing bills using a CASE statement, you can set thresholds based on units consumed. Initial units (0-100) are charged at Rs 7.50, while units exceeding 100 incur Rs 8.50. The SQL syntax formalizes these rules logically and updates the BillAmt field accordingly . This structured approach prevents errors and simplifies large-scale billing operations.
Designing a library database requires careful structuring. Use VARCHAR for fields like Title, Author (with constraints such as 'Not null'), Year as INTEGER, and apply float or decimal types for Price with precision settings like 7,2 to ensure monetary accuracy. Add logical constraints, such as ensuring 'Qty' cannot be negative, by setting constraints at database level . Leverage primary keys for unique book identification to facilitate efficient data retrieval and integrity control.
To manage book records, first create a 'Library' table with fields like Title, Author, Year, Category, and Price. Introduce a new column 'Amount' with 'ALTER TABLE'. To filter books by price range, use: 'SELECT * FROM Library WHERE Price BETWEEN 400 AND 900'. To sort books published from 2010 onwards and priced under 750, apply: 'SELECT * FROM Library WHERE Year >= 2010 AND Price < 750' . Such queries facilitate detailed inventory assessments and timely acquisitions based on pricing and publication year.
Create a table named 'BESCOM' with fields including RRNO (Primary Key), CUSTNAME, BILLDATE, UNITS, and implement a new field, BillAmt, using 'ALTER TABLE'. The bill amount is computed using the rule: MINIMUM Amount Rs. 100; for up to 100 units, Rs 7.50/unit applies, and for units above 100, Rs 8.50/unit applies. Use CASE statements in SQL to apply these rules and update bill amounts: 'UPDATE BESCOM SET BillAmt = CASE WHEN UNITS <= 100 THEN UNITS * 7.50 + 100 ELSE 100*7.50 + (UNITS-100)*8.50 + 100 END' . Display the results with a summary of the maximum, minimum, and total bill amounts using 'SELECT MAX(BillAmt), MIN(BillAmt), SUM(BillAmt) FROM BESCOM' .
In a customer billing context, using 'ORDER BY' clause in SQL enables sorting records. For BESCOM table, organize bills by RRNO with: 'SELECT * FROM BESCOM ORDER BY RRNO'. This streams billing records logically, allowing for easier retrieval and integration, enhancing readability and accessibility of data management .
To manage and display library records with SQL, implement queries that sort data by publication constraints. For example, use 'SELECT * FROM Library WHERE Year > 2010 AND Price < 750 ORDER BY Year ASC' to list all relevant records. This sorting facilitates categorical segregation and resource allocation based on the specified publication year and price limits .
In SQL, duplicate entries in a column can be filtered by using the 'DISTINCT' keyword. For listing unique combination values from the 'Student' table, apply: 'SELECT DISTINCT Combn FROM Student'. This query returns a set of unique academic combinations without duplicates, optimizing storage and ensuring precise combination tracking .
To determine maximum and minimum billing values in an SQL database, utilize the aggregate functions: 'SELECT MAX(BillAmt) AS MaxBill, MIN(BillAmt) AS MinBill FROM BESCOM'. This approach leverages efficient SQL processing capabilities to return the greatest and least values directly from the dataset, facilitating quick summary reporting .