Information Technology · Class 12 Commerce
Ch 3Relational Database Management System - II — Class 12 Information Technology, concept-first.
In the first-year Information Technology course you learned the foundations of a Relational Database Management System (RDBMS) and the basics of MySQL — how data is stored in tables (relations) made of rows (tuples) and columns (attributes), how to create a database and tables with DDL, how to insert, update, delete an…
Key concepts
Hover a concept to preview it and jump to its most relevant Q&A.
Transactions (COMMIT and ROLLBACK)
A transaction is a logical unit of work made of one or more SQL statements that must all succeed or all fail together (atomicity).
Most relevant Q&A
Chapter contents
The NCERT structure, section by section. Open a section to see its questions, then read the concept-first solution.
Recap and the Sample Tables Used in This Chapter
In the first-year Information Technology course you learned the foundations of a Relational Database Management System (RDBMS) and the basics of MySQL — how data is stored in tables (relations) made o…
Database Transactions — COMMIT and ROLLBACK
Many real business operations are not a single change but a group of changes that must all succeed or all fail together.
Handling Missing Values — NULL in MySQL
Sometimes a value in a table is simply not known, not applicable, or not yet entered. In our Employee table, the salary of employee 106 (Ipsita Rout) has not been fixed, so it is shown as NULL.
Sorting the Result — ORDER BY
By default, a SELECT query returns rows in no particular guaranteed order. To present the result sorted, we add an ORDER BY clause, which always comes last in the query.
Manipulating Data — UPDATE and DELETE
Two DML commands change data already stored in a table: UPDATE modifies the values in existing rows, and DELETE removes whole rows.
Changing a Table's Structure — ALTER TABLE
After a table has been created, its structure — the columns it has and their data types — can still be changed with the DDL command ALTER TABLE.
Summarising Data — Aggregate (Group) Functions
So far our queries returned individual rows. Often, though, a business wants a single summary number about many rows — 'how many employees are there?', 'what is the total salary bill?', 'what is the a…
Grouping Records — GROUP BY and HAVING
An aggregate function on its own gives one number for the whole table. The GROUP BY clause lets us instead split the rows into groups that share the same value in one or more columns, and then apply t…
MySQL Built-in Functions I — String Functions
MySQL provides many ready-made built-in functions that perform common operations on values. They are grouped by the kind of data they work on: string (text) functions, mathematical (numeric) functions…
MySQL Built-in Functions II — Mathematical Functions
Mathematical (numeric) functions work on numbers and return a number. They are handy for rounding money amounts, finding remainders, and other calculations inside a query. The common ones are:
MySQL Built-in Functions III — Date and Time Functions
Date and time functions work on DATE, TIME and DATETIME values. They are very useful in business for working out ages, service periods, due dates and the like.
Combining Tables — Cartesian Product, Equi-Join and UNION
A well-designed relational database keeps data in separate related tables to avoid duplication — for example, our Employee table and our Dept table.
Exercises
+−Show 6 questionsHide questions6 questions
- Q1What is a transaction in a database? Explain the terms COMMIT and ROLLBACK with an example of when each is used.Free
- Q2State the four ACID properties of a transaction and write one line on each.Free
- Q3What does NULL represent in a database? Explain why 'Salary = NULL' does not work, and give the correct way to find rows with a missing sala…Preview
- Q7Name the five aggregate functions in SQL and state what each returns. Explain the difference between COUNT(*) and COUNT(column).Preview
- Q14What is a cartesian product of two tables? If table A has 6 rows and table B has 3 rows, how many rows does their cartesian product have? Ho…Preview
- Q16What does the UNION operator do? State the conditions two SELECT statements must satisfy to be combined with UNION, and explain the differen…Preview
More questions
+−Show 10 questionsHide questions10 questions
- Example 4Using the Employee table, write SQL queries to (i) list all employees sorted by salary from highest to lowest, and (ii) list employees order…Free
- Example 5Write SQL statements to (i) give every employee in the 'IT' department a 10% increase in salary, and (ii) delete the record of the employee…Free
- Example 6Write SQL statements to (i) add a new column 'Email' of type VARCHAR(60) to the Employee table, (ii) change the width of the 'Name' column t…Free
- Example 8Using the Employee table, write SQL queries to find (i) the total number of employees, (ii) the total and average salary paid, and (iii) the…Preview
- Example 9Using the Employee table, write an SQL query to display each department along with the number of employees in it and the average salary of t…Preview
- Example 10Using the Employee table, write an SQL query to display only those departments whose average salary is greater than 29000. Explain why HAVIN…Preview
- Example 11Predict the output of the following, assuming str = 'Bhubaneswar': (i) LENGTH('Bhubaneswar'), (ii) UPPER('das'), (iii) CONCAT('Aarti', ' ',…Preview
- Example 12Predict the output of the following mathematical functions: (i) ROUND(2567.856, 2), (ii) TRUNCATE(2567.856, 2), (iii) MOD(17, 5), (iv) POWER…Preview
- Example 13Using the Employee table (JoinDate column), write SQL queries to (i) display each employee's name and the year they joined, and (ii) list th…Preview
- Example 15Using the Employee and Dept tables, write an SQL query (an equi-join) to display each employee's name, their department, and the city in whi…Preview