SQL Practical Exercises for Students
Uploaded by
Maanya KohliSQL Practical Exercises for Students
Uploaded by
Maanya KohliCommon questions
Powered by AIDisplay today’s date in the 'Date/Month/Year' format using: `SELECT DATE_FORMAT(NOW(), '%d/%m/%Y') AS TodayDate;`. Formatting dates is crucial for ensuring consistency across systems and meeting user interface requirements .
You execute `SELECT country, COUNT(customer_id) AS TotalCustomers FROM customers GROUP BY country;`. GROUP BY is key in data analysis for aggregating data; it allows for concise summaries of datasets, identifying trends and insights on customer distribution across different countries .
To insert a new student's details, you use: `INSERT INTO students (student_id, name, marks) VALUES (1001, 'John Doe', 85);`. This command adds a new row to the student table with the specified values for id, name, and marks .
To find the position of the letter 'A' in student names, use: `SELECT name, LOCATE('A', name) AS Position FROM students;`. Understanding string operations aids in data cleaning and parsing, identifying particular patterns or issues within textual data .
You can calculate the remainder of student marks divided by 3 using the SQL command: `SELECT marks, MOD(marks, 3) AS Remainder FROM students;`. Computing the remainder can be useful for tasks such as determining divisibility or categorizing data into specific groups .
To order student records by marks in descending order, you would use: `SELECT student_id, marks FROM students ORDER BY marks DESC;`. This command sorts the student records so that students with higher marks appear first in the results .
The SQL command to compute these aggregate functions on the student marks column would be: `SELECT MIN(marks), MAX(marks), SUM(marks), AVG(marks) FROM students;`. This command retrieves the minimum, maximum, sum, and average values of the marks column from the students table .
You can use SQL string functions to retrieve student names in both uppercase and lowercase by executing: `SELECT UPPER(name) AS Upper, LOWER(name) AS Lower FROM students;`. This command transforms and displays the names in both uppercase and lowercase formats .
The SQL function TRIM can remove spaces from text. Use `SELECT TRIM(BOTH ' ' FROM ' Informatics Practices Class XII ') AS trimmed_text;` to trim spaces from both sides. This is practically applied to clean data, ensuring consistency and storage efficiency by removing unnecessary spaces .
To create a student table with unique student ids, you can use the SQL command: `CREATE TABLE students (student_id INT PRIMARY KEY, name VARCHAR(100), marks INT);`. This command establishes a table with three columns, defining `student_id` as the primary key to ensure uniqueness for each entry.