SQL Practical File: Table Creation & Queries
Uploaded by
czxharshpreetSQL Practical File: Table Creation & Queries
Uploaded by
czxharshpreetCommon questions
Powered by AICreate SQL queries that specify IS NULL in WHERE clauses to target null fields, such as displaying books where the publisher value is NULL. This necessitates a strategic approach to data analysis to exclude or include missing data contextually.
Utilize JOIN queries to merge data from EMPLOYEE and JOB tables. For example, a query showing employees with sales over 130000 and their job titles requires combining these tables based on JOBID and applying a WHERE clause for the sales condition, thereby integrating table relationships for richer queries.
Sorting functions such as ORDER BY can arrange data in ascending or descending order based on specified columns like NAME or SCORE. The choice of criteria depends on the data analysis objective, such as alphabetizing names or ranking scores for readability or insights.
SQL uses functions like LIKE and wildcard characters to find patterns in text fields, useful when querying names containing substrings like 'SINGH'. This facilitates sophisticated textual data searches, aiding in refined databases filtering and pattern recognition within dataset text fields.
Use SELECT statements combined with WHERE clauses to filter results. For example, SELECT Book name, Author name, and Price from the BOOKS table where the publisher is 'First Publ.' or the book type is 'Text'. This precise filtering using conditions helps in extracting relevant dataset subsets.
The SQL commands include ALTER to add, modify, or drop attributes ('SEX' attribute), UPDATE to increase marks for specific conditions ('increase marks by 10 for students scoring less than 20'), ORDER BY to sort data ('by NAME or SCORE'), DELETE to remove entries ('where SCORE is less than 10'), and GROUP BY to compute aggregate functions ('min, max, sum, count, average')
First, use the ALTER command to modify the table structure, such as adding a new attribute or changing data types. Then, apply the UPDATE command to increase the salary by a specific percentage (10% in this case). This requires understanding the current data and its constraints to make the necessary adjustments accurately.
Apply an SQL UPDATE statement with a WHERE clause that specifies the unique identifier, such as an employee ID. For example, changing JobId for Employee E4 involves setting a new value where EID equals 'E4'. This precise targeting ensures that only the intended records are modified.
Use GROUP BY statements combined with aggregate functions (SUM, AVG) to compute summaries per group. For instance, to find the total salary based on gender, group data by 'Sex' in the table and sum 'Pay'. This organizing technique allows efficient extraction of aggregated financial insights.
Using GROUP BY with aggregate functions like MIN, MAX, SUM, COUNT, and AVG, one can derive detailed statistics categorized by groups, such as departments or publishers. This technique enables comprehensive data examination, uncovering trends and averages contextualized by specific attributes.