Samacheer Kalvi Class 12 Computer Science Solutions Chapter 12 Structured Query Language (SQL)

I. Choose the Best Answer (1 Marks)

 

Question 1. Which commands provide definitions for creating table structure, deleting relations and modifying relation schemas?
(a) DDL
(b) DML
(c) DCL
(d) DQL
Answer: (a) DDL
In simple words: DDL commands help you set up and change the basic layout of your database, like making new tables or changing existing ones. They define how data is structured.

🎯 Exam Tip: Remember DDL stands for Data Definition Language, focusing on the schema, while DML is for data manipulation, and DCL for data control.

 

Question 2. Which command lets you change the structure of the table?
(a) SELECT
(b) ORDER BY
(c) MODIFY
(d) ALTER
Answer: (d) ALTER
In simple words: The ALTER command is like a tool that lets you change how a table is built, for example, adding a new column or changing the type of data in an existing one. It's used to make structural changes after a table is created.

🎯 Exam Tip: Distinguish between ALTER, which changes table structure, and UPDATE, which changes data *within* the table.

 

Question 3. The command to delete a table is
(a) DROP
(b) DELETE
(c) DELETE ALL
(d) ALTER TABLE
Answer: (a) DROP
In simple words: If you want to get rid of an entire table and all the data in it, you use the DROP command. It completely removes the table from your database.

🎯 Exam Tip: Be cautious with the DROP command as it permanently removes the entire table and its data without a recycle bin.

 

Question 4. Queries can be generated using
(a) SELECT
(b) ORDER BY
(c) MODIFY
(d) ALTER
Answer: (a) SELECT
In simple words: To ask the database for information or to see data, you use the SELECT command. It helps you pick out the data you need from tables.

🎯 Exam Tip: The SELECT statement is the most frequently used SQL command and is essential for retrieving data.

 

Question 5. The clause used to sort data in a database
(a) SORT BY
(b) ORDER BY
(c) GROUP BY
(d) SELECT
Answer: (b) ORDER BY
In simple words: When you want to see your data arranged in a specific order, like alphabetically or by numbers from smallest to largest, you use the ORDER BY clause. This helps you present information neatly.

🎯 Exam Tip: Remember that `ORDER BY` sorts the final results, while `GROUP BY` collects rows that have the same values into summary rows.

II. Answer the Following Questions (2 Marks)

 

Question 1. Write a query that selects all students whose age is less than 18 in order wise.
Answer: The query to select all students whose age is 18 or less, ordered by their name, is:
`SELECT * FROM STUDENT WHERE AGE <= 18 ORDER BY NAME;`
This command will fetch all columns (`*`) from the `STUDENT` table, but only for those students where their `AGE` is 18 or less. Finally, the results will be neatly arranged alphabetically based on their `NAME`.
In simple words: This command shows all details of students who are 18 years old or younger, sorted by their names.

🎯 Exam Tip: Always include the `ORDER BY` clause at the end of your `SELECT` statement to ensure the results are presented in the required sorted manner.

 

Question 2. Differentiate Unique and Primary Key constraint
Answer: Here is how Unique Key and Primary Key constraints are different:

Both keys help keep data unique, but the Primary Key is the main one used to organize the table and cannot be empty, ensuring every row is identifiable.
In simple words: Both Unique Key and Primary Key stop duplicate information. A Primary Key is the main way to identify each row and can never be empty. A Unique Key also ensures uniqueness but can sometimes be empty, and a table can have many Unique Keys.

🎯 Exam Tip: Remember that a Primary Key uniquely identifies each row and cannot be null, while a Unique Key allows nulls and a table can have multiple unique keys.

 

Question 3. Write the difference between table constraint and column constraint?
Answer: The main difference between table constraints and column constraints is how broadly they apply across a table:
Column Constraint:
Column constraints only apply to a single column in a table. For instance, if you set a rule that a specific column cannot be empty, that rule only affects that one column.
Table Constraint:
Table constraints apply to a group of one or more columns in a table. This means the rule might involve values from several columns at once, or it might apply to the table as a whole. For example, a rule that combines two columns to make a unique identifier is a table constraint.
Table constraints offer more flexibility because they can consider relationships between data in different columns, providing a more comprehensive way to maintain data accuracy.
In simple words: Column constraints are rules for just one column. Table constraints are rules that can use one or more columns together, or even the whole table.

🎯 Exam Tip: Understand that column constraints ensure data integrity within a single column, while table constraints ensure integrity across multiple columns or the entire table, often for complex relationships.

 

Question 4. Which component of SQL lets insert values in tables and which lets to create a table?
Answer: In SQL, different commands are used for creating tables and inserting values into them:
Creating a Table: The `CREATE TABLE` command, which is part of DDL (Data Definition Language), is used to make a new table structure. This command defines the columns and their data types.
Inserting Values into Tables: The `INSERT INTO` command, which is part of DML (Data Manipulation Language), is used to add new rows (values) into an existing table. This command populates the table with actual data.
These commands are fundamental for building and filling a database.
In simple words: To make a new table, you use `CREATE TABLE`. To put information into that table, you use `INSERT INTO`.

🎯 Exam Tip: Differentiate between DDL (Data Definition Language) commands like CREATE TABLE (for structure) and DML (Data Manipulation Language) commands like INSERT INTO (for data).

 

Question 5. Write short notes on SQL and MySQL?
Answer: Here are short notes on SQL and MySQL:
SQL (Structured Query Language): SQL is a special language used to talk to databases. It helps people manage and work with data stored in a database. You can use SQL commands to do things like create new tables, get information from tables, change table structures, and move data around. It's the standard language for relational databases. SQL is not a full programming language but a specialized language for data management.
MySQL: MySQL is a popular type of database management system, often called an RDBMS (Relational Database Management System). It's a software that helps organize and store data in tables. Like other RDBMS packages (such as SQL Server, Oracle, and PostgreSQL), MySQL uses SQL as its language to manage the data. It is widely used for web applications because it is reliable and easy to use.
In simple words: SQL is the language used to talk to databases. MySQL is a program that stores and manages data, and it understands SQL commands.

🎯 Exam Tip: Remember that SQL is a language, while MySQL is a specific database system that *uses* SQL.

III. Answer the Following Questions (3 Marks)

 

Question 1. What is a constraint? Write short note on Primary key constraint.
Answer:
Constraint: A constraint is a rule or condition that is applied to a field (column) or a group of fields in a database. These rules help to limit what kind of data can be entered into the table, making sure the data stays accurate and trustworthy. Constraints can be set at either the column level (for a single column) or the table level (for multiple columns or the whole table). They ensure data integrity and prevent invalid data entries.
Primary Key Constraint: The Primary Key constraint is a special rule that marks a field (or a group of fields) as the main identifier for each record in a table. Its job is to make sure every record can be found uniquely. A table can only have one Primary Key. This constraint is like a Unique Key constraint, but with an important extra rule: a Primary Key field cannot have any NULL (empty) values. This means a field declared as a Primary Key must also always have the NOT NULL constraint applied to it, ensuring every record is always identifiable.
In simple words: A constraint is a rule for data in a table to keep it correct. A Primary Key is a special rule that makes sure each row has a unique identifier and this identifier can never be empty.

🎯 Exam Tip: Clearly define what a constraint is before describing the Primary Key, and emphasize that a Primary Key must always be unique and not null.

 

Question 2. Write a SQL statement to modify the student table structure by adding a new age column.
Answer: To modify the `student` table structure by adding a new `age` column, you can use the `ALTER TABLE` command.
Syntax:
`ALTER TABLE ADD [];`
Example:
To add an `age` column of type `integer` with a size of 3 digits to the `student` table, the SQL statement would be:
`ALTER TABLE student ADD age integer (3);`
This command changes the structure of the existing `student` table without losing any data.
In simple words: You use `ALTER TABLE` and `ADD` to put a new column, like 'age' (which holds numbers), into an existing table named 'student'.

🎯 Exam Tip: Remember to specify the data type and size for the new column when using the `ALTER TABLE ADD` command.

 

Question 3. Write any three DDL commands.
Answer: Here are three common DDL (Data Definition Language) commands:
a. `CREATE TABLE` Command: This command is used to make a brand new table in the database. You define the table's name, its columns, and the type of data each column will hold. For example:
`CREATE TABLE Student`
`(`
  `Admno integer,`
  `Name char(20),`
  `Gender char(1),`
  `Age integer,`
  `Place char(10)`
`);`
b. `ALTER` Command: The `ALTER` command is used to change the structure of an existing table. This can include adding a new column, changing the data type or size of an existing column, or renaming/deleting a column from the table. For example, to add an `address` column to the `Student` table:
`ALTER TABLE Student ADD address char;`
c. `DROP TABLE` Command: This command is used to completely remove a table from the database. When a table is dropped, all its data and its structure are permanently deleted. For example:
`DROP TABLE Student;`
These commands are critical for defining and managing the schema of a database.
In simple words: `CREATE TABLE` makes a new table. `ALTER` changes an existing table. `DROP TABLE` deletes a table completely.

🎯 Exam Tip: When listing DDL commands, always provide a brief description of their function and a simple, clear example for each.

 

Question 4. Write the use of the Savepoint command with an example.
Answer: The `SAVEPOINT` command is used in database transactions to set a temporary saving point. This allows you to roll back (undo changes) to a specific point within a transaction, rather than rolling back the entire transaction.
This means you can save different stages of changes to your table. If something goes wrong after a `SAVEPOINT`, you can undo only the changes made *after* that savepoint, using the `ROLLBACK TO` command, keeping the earlier changes intact.
Example:
Let's consider an existing `Student` table:

Now, let's insert a new student and then commit the changes:
`INSERT INTO Student VALUES (107, 'Beena', 'F', 20, 'Cochin');`
`COMMIT;`
After this, the table (`Table 2`) will look like this:
Now, let's update a student's name and set a `SAVEPOINT`:
`UPDATE Student SET Name = 'Mini' WHERE Admno=105; SAVEPOINT A;`
The table (`Table 3`) will now show:
Now, let's insert another new student and set another `SAVEPOINT`:
`INSERT INTO Student VALUES(108, 'Jisha', 'F', 19, 'Delhi'); SAVEPOINT B;`
The table (`Table 4`) will now include this student:
If we now give the `ROLLBACK TO A;` command, the changes made after `SAVEPOINT A` (which was inserting 'Jisha') will be undone, and the table will revert to `Table 3` again, removing 'Jisha'. This shows how `SAVEPOINT` helps control rollbacks.
In simple words: `SAVEPOINT` lets you create temporary marks in your database work. If you make a mistake later, you can `ROLLBACK` to an earlier `SAVEPOINT` instead of undoing everything.

🎯 Exam Tip: Remember that `SAVEPOINT` allows for partial rollbacks within a transaction, providing finer control over changes compared to a full `ROLLBACK`.

 

Question 5. Write a SQL statement using a DISTINCT keyword.
Answer: The `DISTINCT` keyword is used with the `SELECT` command to show only the unique values in a column, removing any duplicate entries. This helps to get rid of repeated data, giving a clean list of unique items.
Example:
Let's say you have a `Student` table with many students, and you want to see all the unique `Place` values where they live.
`SELECT DISTINCT Place FROM Student;`
This statement will show only the unique `Place` names, removing any duplicates. For example, if many students are from Chennai, it will list "Chennai" only once.
Output:

In simple words: The `DISTINCT` keyword in a `SELECT` statement helps you see only the unique items in a list, hiding all the repeated ones.

🎯 Exam Tip: Use `DISTINCT` when you need to retrieve a list of unique values from a column, particularly useful for categorical data.

IV. Answer the Following Questions (5 Marks)

 

Question 1. Write the different types of constraints and their functions.
Answer: Constraints are rules that help maintain data quality and accuracy in a database. They limit the type of data that can be stored and ensure consistency. Here are the different types of constraints and their functions:
The different types of constraints are:
1. Unique Constraint
2. Primary Key Constraint
3. Default Constraint
4. Check Constraint
5. Table Constraint

Let's look at each type in detail:
1. Unique Constraint:
This constraint makes sure that no two rows (records) in the specified column(s) have the same value. For example, if you apply a `UNIQUE` constraint on the `Admno` (Admission Number) column of a student table, it ensures that no two students can have the same admission number. This constraint can be used as follows:
`CREATE TABLE Student (`
  `Admno integer NOT NULL UNIQUE,`
  `Name char(20) NOT NULL,`
  `Gender char(1),`
  `Age integer,`
  `Place char(10)`
`);`
The `UNIQUE` constraint can only be applied to fields that have also been marked as `NOT NULL`, though it can allow one NULL value if `NOT NULL` is not specified. When both `NOT NULL` and `UNIQUE` are applied to a single field, it's called multiple constraints. This ensures the field must have a value and that value must be unique.

2. Primary Key Constraint:
This constraint marks a field as the main identifier that helps to uniquely identify each record in a table. A table can only have one `PRIMARY KEY`. It is similar to a `UNIQUE` constraint, but a `PRIMARY KEY` does not allow any `NULL` values. Therefore, a field declared as a `PRIMARY KEY` must always have the `NOT NULL` constraint. This ensures every row is uniquely identified and always has an identifier. For example:
`CREATE TABLE Student (`
  `Admno integer NOT NULL PRIMARY KEY,`
  `Name char(20) NOT NULL,`
  `Gender char(1),`
  `Age integer,`
  `Place char(10)`
`);`
In this example, `Admno` is set as the primary key. It uniquely identifies each student and cannot be empty.

3. Default Constraint:
The `DEFAULT` constraint is used to set a default value for a field. If you don't provide a value for a specific field when adding a new record, the default value will be automatically assigned to it. This helps ensure that fields are never left blank unless intended. For example:
`CREATE TABLE Student (`
  `Admno integer NOT NULL PRIMARYKEY,`
  `Name char(20) NOT NULL,`
  `Gender char(1),`
  `Age integer DEFAULT 17,`
  `Place char(10)`
`);`
Here, if `Age` is not provided when inserting a new student, it will automatically be set to 17.

4. Check Constraint:
The `CHECK` constraint helps to set a specific limit or condition for the values that can be entered into a field. When you define a `CHECK` constraint on a column, it only allows values that meet the specified rule. This ensures data falls within an acceptable range or set of options. For example:
`CREATE TABLE Students (`
  `Admno integer NOT NULL PRIMARYKEY,`
  `Name char(20) NOT NULL,`
  `Gender char(1),`
  `Age integer (CHECK<=19),`
  `Place char(10)`
`);`
In this example, the `Age` field will only accept values that are less than or equal to 19. The `CHECK` constraint can use logical operators for complex conditions.

5. Table Constraint:
A table constraint is a rule that applies to a group of fields in a table, rather than just a single field. It's usually defined at the end of the table definition. This type of constraint is used when a rule needs to consider values from more than one column. For example, you might create a table named `Student1` with fields like `Admno`, `Firstname`, `Lastname`, `Gender`, `Age`, `Place`. If you define `PRIMARY KEY (Firstname, Lastname)`, it creates a table constraint because the primary key relies on two fields working together.
`CREATE TABLE Student 1 (`
  `Admno integer NOT NULL,`
  `Firstname char(20),`
  `Lastname char(20),`
  `Gender char(1),`
  `Age integer,`
  `Place char(10),`
  `PRIMARY KEY (Firstname, Lastname)`
`);`
In this case, the combination of `Firstname` and `Lastname` must be unique for each record. This ensures that even if two students have the same first name or last name, their full name combination is unique.
In simple words: Constraints are rules that keep database data correct. Unique makes sure no two items are exactly the same. Primary Key is a special unique key that can never be empty. Default puts a value in if you don't provide one. Check sets limits on what values a column can hold. Table constraints apply rules across many columns, like making a unique ID from a first and last name.

🎯 Exam Tip: For a 5-mark question, clearly define each constraint type, provide a brief function, and a simple SQL example to illustrate its usage.

 

Question 2. Consider the following employee table. Write SQL commands for the questions. (i) to (v).

Answer:
(i) To display the details of all employees in descending order of pay.
SELECT * FROM Employee ORDER BY PAY DESC;
(ii) To display all employees whose allowance is between 5000 and 7000.
SELECT * FROM Employee WHERE ALLOWANCE BETWEEN 5000 AND 7000;
(iii) To remove the employees who are mechanics.
DELETE FROM Employee WHERE DESIG='Mechanic';
(iv) To add a new row.
INSERT INTO Student VALUES(106, 'Ram', 'Clerk',15000, 6500);
(v) To display the details of all employees who are operators.
SELECT * FROM Employee WHERE DESIG='Operator';
In simple words: SQL commands help us manage data in tables. We can sort employee details by pay, find those with specific allowances, remove certain types of employees, add new ones, and list only employees in a certain role.

🎯 Exam Tip: Remember to use `ORDER BY` for sorting, `WHERE` for filtering, `DELETE FROM` for removal, and `INSERT INTO` for adding data. Always check syntax for each command.

 

Question 3. What are the components of SQL? Write the commands in each.
Answer:

The various components of SQL are:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Query Language (DQL)
• Transactional Control Language (TCL)
• Data Control Language (DCL)

Data Definition Language (DDL):

• DDL statements define the database structure or schema. They handle the "blueprint" of the database.
• DDL deals with descriptions of the database schema and is used to create and modify the structure of database objects like tables. This helps organize how data is stored.
• DDL provides definitions to specify the storage structure and access methods used by the database system, ensuring data is managed efficiently.

A DDL performs the following functions:

• It helps identify the type of data division, such as data item, segment, record, and database file. This helps categorize information.
• It gives a unique name to each data item type, record type, file type, and database, ensuring clear identification.
• It defines the size of each data item, setting limits for storage.
• It may define the range of values that a data item can hold, preventing invalid entries.
• It may specify privacy locks to prevent unauthorized data entry, keeping data secure.

SQL commands which come under Data Definition Language are:

Data Manipulation Language (DML):

• A Data Manipulation Language (DML) is a computer programming language used for adding (inserting), removing (deleting), and modifying (updating) data in a database. It lets users interact with the data itself.
• In SQL, DML comprises statements that change stored data but not the database's schema or structure.
• After the database schema has been set up, data can be manipulated using procedures expressed by DML, allowing for dynamic data management.

The DML is basically of two types:

• Procedural DML – This type requires a user to specify what data is needed and how to get it, offering precise control.
• Non-Procedural DML – This type requires a user to specify what data is needed without specifying how to get it, making it simpler for some tasks.

SQL commands which come under Data Manipulation Language are:

Data Control Language (DCL):

• A Data Control Language (DCL) is a programming language used to control access to data stored in a database, ensuring security.
• It is used for controlling privileges (authorization) in the database, determining who can do what.
• Privileges are needed for performing database operations such as creating sequences, views, and tables. These permissions keep the database safe.

SQL commands which come under Data Control Language are:

Transactional Control Language (TCL):

• Transactional Control Language (TCL) commands are used to manage transactions in the database, making sure changes are handled correctly.
• These commands manage changes made to data in a table by DML statements, ensuring data consistency.

SQL commands which come under Transactional Control Language are:

Data Query Language (DQL):

• The Data Query Language consists of commands used to query or retrieve data from a database. This is how users ask for information.
• One such SQL command in Data Query Language is `SELECT`, which displays records from the table.

In simple words: SQL has different types of commands to handle databases. DDL helps create and change table structures. DML helps add, change, or remove data. DCL gives or takes away permissions. TCL helps manage groups of changes, and DQL helps find and show data.

🎯 Exam Tip: Knowing the purpose and common commands for each SQL component (DDL, DML, DCL, TCL, DQL) is crucial for understanding how databases are managed and interacted with.

 

Question 4. Construct the following SQL statements in the student table
(i) SELECT statement using GROUP BY clause.
(ii) SELECT statement using ORDER BY clause.
Answer:

Table: student

(i) SELECT statement using GROUP BY clause:

The GROUP BY clause is used with the SELECT statement to group rows or columns that have the same values. This helps divide the table into meaningful groups. For example, to find out how many male or female students are in a class, the GROUP BY clause can be used. It works well with aggregate functions to create summary reports from the database. It helps to simplify and organize data for better understanding.

Syntax for the GROUP BY clause:
SELECT < column-list >, aggregate_function(column_name) FROM table_name GROUP BY < column-name > HAVING condition;

Example: To apply the above command on the student table:
SELECT Gender FROM Student GROUP BY Gender;

The command will give the following result:

Only two results are returned because there are only two gender types, Male and Female. The GROUP BY clause groups all 'M' students together into one row and all 'F' students into one row. For example, to count the number of male and female students in the student table, the following command is given:

SELECT Gender, count(*) FROM Student GROUP BY Gender;

The GROUP BY clause applies aggregate functions independently to a series of rows that have a common value in a field. The above SELECT statement counts the number of Male and Female students.

(ii) SELECT statement using ORDER BY clause:

The ORDER BY clause in SQL is used to sort data in either ascending or descending order based on one or more columns. By default, ORDER BY sorts data in ascending order. We can use the keyword `DESC` to sort data in descending order and `ASC` to sort in ascending order. Sorting data helps in quickly finding specific information.

The ORDER BY clause is used as:
SELECT < column – name1 >, < column – name2 > ... FROM < table-name > ORDER BY < column 1 > < column 2 > ... ASC | DESC;

Example: To display students in alphabetical order of their names, the command is:
SELECT * FROM Student ORDER BY Name;

The student table arranged by name is as follows:

In simple words: GROUP BY helps us put similar data together, like counting how many boys and girls there are. ORDER BY helps us arrange the data, like listing names from A to Z or numbers from smallest to largest. These commands make it easier to understand and find information in a big table.

🎯 Exam Tip: Remember that `GROUP BY` is used for aggregating data, often with functions like `COUNT()` or `SUM()`, while `ORDER BY` is solely for arranging the display order of results. Practice with both `ASC` (ascending) and `DESC` (descending) keywords.

 

Question 5. Write a SQL statement to create a table for employees having any five fields and create a table constraint for the employee table.
Answer:

Creating an Employee Table with Table Constraint:
CREATE TABLE Employee(
empno integer NOT NULL,
empfname char(20),
emplname char(20),
Designation char(20),
Basicpay integer,
PRIMARY KEY (empfname,emplname)
);

In simple words: To make a new table for employees, we give it a name like 'Employee'. Then, we list out all the details we want to store, like employee number, first name, last name, job title, and salary. We also set a special rule, called a primary key, using the first and last names together to make sure each employee record is unique. This table constraint helps keep the data organized and error-free.

🎯 Exam Tip: When creating tables, always define a primary key to ensure unique records. Also, use `NOT NULL` for fields that must always have a value, like `empno` in this example.

 

12th Computer Science Guide International Economics Organisations Additional Important Questions And Answers

 

I. Choose The Best Answer (1 Mark)

 

Question 1. The SQL was called as __________ in the early 1970s.
(a) squel
(b) sequel
(c) seqel
(d) squeal
Answer: (b) sequel
In simple words: The first name for SQL back in the 1970s was 'sequel'. This name was used before it became known as SQL.

🎯 Exam Tip: Historical details like original names of technologies are often tested. Remembering key terms and their origins can help with multiple-choice questions.

 

Question 2. Which of the following language was designed for managing and accessing data in RDBMS?
(a) DBMS
(b) DDL
(c) DML
(d) SQL
Answer: (d) SQL
In simple words: SQL is a special computer language made to help people work with databases that use tables to store information. It lets you find, add, change, or remove data easily.

🎯 Exam Tip: Understand that SQL is the standard language for relational databases (RDBMS), while DDL and DML are *categories* of SQL commands, not the language itself.

 

Question 3. SQL stands for
(a) Standard Query Language
(b) Structured Query Language
(c) Structural Query Language
(d) Simple Query Language
Answer: (c) Structural Query Language
In simple words: SQL is a computer language used to manage data in databases. Its name, Structural Query Language, tells us it helps structure how we ask questions to the database.

🎯 Exam Tip: While "Structured Query Language" is the widely accepted full form, sometimes older or alternative definitions like "Structural Query Language" appear in specific textbooks or historical contexts. Always refer to your curriculum's exact definition.

 

Question 4. Expand ANSI __________
(a) American North-South Institute
(b) Asian North Standard Institute
(c) American National Standard Institute
(d) Artie National Standard Institute
Answer: (c) American National Standard Institute
In simple words: ANSI is a group in America that makes sure many things, including computer languages like SQL, follow common rules so they work everywhere. It sets standards for many industries.

🎯 Exam Tip: Acronyms are common in computer science. Knowing their full forms, especially for organizations that set standards, is important for general knowledge and exams.

 

Question 5. The latest SQL standard as of now is
(a) SQL 2008
(b) SQL 2009
(c) SQL 2018
(d) SQL 2.0
Answer: (a) SQL 2008
In simple words: SQL, like other computer languages, gets updated over time. The version called SQL 2008 was the most current standard at a particular point, making sure everyone used the same rules for databases.

🎯 Exam Tip: SQL standards evolve; it's useful to know the major versions. Always confirm the most recent standard relevant to your course curriculum.

 

Question 6. In database objects, the data in RDBMS is stored
(a) Queries
(b) Languages
(c) Relations
(d) Tables
Answer: (d) Tables
In simple words: In a relational database system (RDBMS), all the information is kept in tables, which are like simple grids with rows and columns. These tables help organize the data clearly.

🎯 Exam Tip: Understand that tables are the fundamental storage units in RDBMS, and "relations" is the mathematical term for these tables.

 

Question 7. DDL expansion is
(a) Data Defined Language
(b) Data Definition Language
(c) Definition Data Language
(d) Dictionary Data Language
Answer: (b) Data Definition Language
In simple words: DDL stands for Data Definition Language. It's a part of SQL that helps us create, change, or remove the structure of a database, like tables and columns.

🎯 Exam Tip: DDL is one of the core categories of SQL commands. Remember its full form and that it deals with the *structure* (schema) of the database.

 

Question 8. Identify which is not an RDBMS package __________
(a) MySQL
(b) IBMDB2
(c) MS-Access
(d) Php
Answer: (d) Php
In simple words: MySQL, IBMDB2, and MS-Access are all types of database systems or software that manage data in tables. Php, however, is a programming language used to build websites, not a database system itself.

🎯 Exam Tip: It's important to distinguish between database management systems (like MySQL) and programming languages (like PHP) that interact with them. Both are key in web development.

 

Question 9. __________ component of SQL includes commands to insert, delete and modify tables in database
(a) DCL
(b) DML
(c) TCL
(d) DDL
Answer: (b) DML
In simple words: DML stands for Data Manipulation Language. It is the part of SQL that has commands to add new data, remove existing data, and change data inside the tables of a database.

🎯 Exam Tip: Remember that DML (Manipulation) deals with the *data within* tables, while DDL (Definition) deals with the *structure of* tables and the database itself.

 

Question 10. __________ command removes all records from a table and also release the space occupied by these records.
(a) Drop
(b) Truncate
(c) ALTER
(d) Delete
Answer: (b) Truncate
In simple words: The `TRUNCATE` command is special because it not only clears all the data from a table but also frees up the memory space that data used to take. It's like resetting the table completely.

🎯 Exam Tip: Differentiate `TRUNCATE`, `DELETE`, and `DROP`. `TRUNCATE` clears data and resets space quickly. `DELETE` clears data row by row, is slower, and can be undone. `DROP` removes the entire table structure.

 

Question 11. WAMP stands for
(a) Windows, Android, MySQL, PHP
(b) Windows, Apache, MySQL, Python
(c) Windows, APL, MySQL, PHP
(d) Windows, Apache, MySQL, PHP
Answer: (d) Windows, Apache, MySQL, PHP
In simple words: WAMP is a group of software programs often used together to build websites. W stands for Windows, A for Apache (a web server), M for MySQL (a database), and P for PHP (a programming language). This bundle makes it easy to set up a local web development environment.

🎯 Exam Tip: WAMP is a popular local server stack for Windows. Similar stacks include XAMPP (cross-platform), LAMP (Linux), and MAMP (macOS). Knowing these acronyms and their components is useful in web development questions.

 

Question 12. __________ is the vertical entity that contains all information associated with a specific field in a table
(a) Field
(b) tuple
(c) row
(d) record
Answer: (a) Field
In simple words: In a table, a field is like a single box where you put one kind of information, such as a name or an age. All the data for that particular type goes into that vertical column.

🎯 Exam Tip: In database terminology, a "field" often refers to a column or an attribute. A "row" or "tuple" refers to a single record containing data across all fields for one entity.

 

Question 13. __________ is a collection of related fields or columns in a table.
(a) Attributes
(b) SQL
(c) Record
(d) Relations
Answer: (c) Record
In simple words: A record is like one complete entry in a table. For example, if you have a student table, one student's full information (like their name, age, and class) all together forms one record. This is also called a row.

🎯 Exam Tip: A "record" (or "row" or "tuple") is a fundamental concept representing a single, complete entry of data across all fields in a database table.

 

Question 14. SQL standard recognized only Text and Number data type.
(a) ANSI
(b) TCL
(c) DML
(d) DCL
Answer: (a) ANSI
In simple words: The ANSI standard for SQL primarily focused on text and number data types. It provided the basic rules for these fundamental data types in databases.

🎯 Exam Tip: When faced with questions that seem like incomplete statements, check the options to understand what kind of answer is expected. Here, the options point to a standard body or command type.

 

Question 15. __________ data type is same as real expect the precision may exceed 64?
(a) float
(b) real
(c) double
(d) long real
Answer: (c) double
In simple words: The 'double' data type is similar to 'real' for storing decimal numbers, but it can hold numbers with much more precision (more digits after the decimal point), often beyond 64 bits. This means it can store very exact numbers.

🎯 Exam Tip: Understanding data types is critical. `Float`, `Real`, and `Double` are used for numbers with decimal points, with `Double` offering the highest precision for very accurate calculations.

 

Question 16. __________ column constraint enforces a field to always contain a value.
(a) NULL
(b) "NOT NULL"
(c) YES
(d) ALWAYS
Answer: (b) "NOT NULL"
In simple words: The "NOT NULL" rule in a database table makes sure that a specific column cannot be left empty. Every time you add data, you must put something in that field. This helps keep important information from being missed.

🎯 Exam Tip: The `NOT NULL` constraint is essential for data integrity, ensuring that critical fields always have values and preventing missing information in records.

 

Question 17. ______________ is often used for web development and internal testing.
(a) Windows
(b) Google
(c) WAMP
(d) Google Chrome
Answer: (c) WAMP
In simple words: WAMP is a software package that helps people create websites on their own computer. It includes Windows for the operating system, Apache as the web server, MySQL for the database, and PHP for scripting. This makes it easy to test websites locally before putting them online.

🎯 Exam Tip: Remember WAMP and XAMPP are common local server environments, often distinguished by their operating system. WAMP is for Windows, while XAMPP is cross-platform.

 

Question 18. To work with the databases, the command used is ______________ database
(a) create
(b) modify
(c) use
(d) work
Answer: (c) use
In simple words: The "use" command helps you pick which database you want to work with from a list of databases. It is like telling the system, "I want to work with this specific database now."

🎯 Exam Tip: In SQL, the `USE` statement is crucial for selecting the active database you wish to perform operations on.

 

Question 19. ______________ is not a SQL TCL command.
(a) Commit
(b) Rollback
(c) Revoke
(d) Savepoint
Answer: (c) Revoke
In simple words: Among the choices, Commit, Rollback, and Savepoint are all used to manage how changes are saved in a database transaction. Revoke, however, is used for taking away permissions, so it is not a Transaction Control Language command. Revoke belongs to Data Control Language (DCL).

🎯 Exam Tip: Clearly differentiate between TCL (Transaction Control Language) commands like COMMIT, ROLLBACK, SAVEPOINT and DCL (Data Control Language) commands like GRANT and REVOKE.

 

Question 20. ______________ provides a set of definitions to specify the storage structure used by the database system.
a) DML
b) DQL
c) DDL
d) TCL
Answer: (c) DDL
In simple words: DDL stands for Data Definition Language, and it is used to create, change, or remove the structure of a database, like tables and their columns. It deals with how the database is organized, not the actual data inside it.

🎯 Exam Tip: Remember DDL focuses on the `schema` (structure), while DML focuses on the `data` (records).

 

Question 21. Which among the following is not a WAMP?
(a) PHP
(b) MySQL
(c) DHTML
(d) Apache
Answer: (c) DHTML
In simple words: WAMP stands for Windows, Apache, MySQL, and PHP. These are specific components that work together for web development. DHTML (Dynamic HTML) is a web development technique, not a server component like the others.

🎯 Exam Tip: Know the full forms and purpose of common acronyms like WAMP to identify which components are part of it and which are not.

 

Question 22. ______________ command used to create a table.
a) CREATE
b) ALTER TABLE
c) NEW TABLE
d) DDL TABLE
Answer: (a) CREATE
In simple words: The "CREATE" command is the one you use when you want to make a new table in your database. You will typically follow it with "TABLE" and then the name of your new table.

🎯 Exam Tip: The `CREATE TABLE` statement is a fundamental DDL command used to define the structure of new tables in a database.

 

Question 23. ______________ keyword is used to sort the records in ascending order.
a) ASCD
b) ASD
c) ASCE
d) ASC
Answer: (d) ASC
In simple words: In SQL, when you want to arrange your data from smallest to largest, or A to Z, you use the "ASC" keyword after "ORDER BY." It means "ascending" order. If you do not specify, ascending is the default order.

🎯 Exam Tip: Always remember `ASC` for ascending and `DESC` for descending order when using the `ORDER BY` clause in SQL queries.

 

Question 24. Which command changes the structure of the database?
(a) update
(b) alter
(c) change
(d) modify
Answer: (b) alter
In simple words: The "ALTER" command in SQL is used to make changes to the existing structure of a database object, like adding a new column to a table or changing a column's data type. It allows you to modify the design of your database after it has been created.

🎯 Exam Tip: `ALTER TABLE` is used to change table structure, `UPDATE` is used to change data within table rows, and `MODIFY` is often a sub-clause of `ALTER` for column changes.

 

Question 25. ______________ clause is used to divide the table into groups.
a) DIVIDE BY
b) ORDER BY
c) GROUP BY
d) HAVING
Answer: (c) GROUP BY
In simple words: The "GROUP BY" clause helps you put rows that have the same values into summary groups. For example, you can group all students by their gender to count how many boys and girls there are. It is very useful for getting summary information.

🎯 Exam Tip: `GROUP BY` is commonly used with aggregate functions (like COUNT, SUM, AVG) to perform calculations on each group of data.

 

Question 26. ______________ command is used to gives permission to one or more users to perform specific tasks.
a) GIVE
b) ORDER
c) GRANT
d) WHERE
Answer: (c) GRANT
In simple words: The "GRANT" command in SQL is how you give different users permission to do things like view, add, change, or delete information in your database. It controls who can access what.

🎯 Exam Tip: `GRANT` and `REVOKE` are the two main DCL (Data Control Language) commands for managing user access rights.

 

Question 27. How many types of DML commands are there?
(a) 1
(b) 2
(c) 3
(d) 4
Answer: (b) 2
In simple words: There are two main types of DML commands: Procedural DML and Non-Procedural DML. Procedural DML tells the system exactly how to get data, while Non-Procedural DML only says what data is needed, not how to get it.

🎯 Exam Tip: The two fundamental types of DML are Procedural (specifying both *what* and *how*) and Non-Procedural (specifying only *what* data is needed).

 

II. Answer the following questions (2 and 3 Marks)

 

Question 1. Write a note on RDBMS?
Answer: RDBMS stands for Relational Database Management System. It is a type of database system that stores data in tables, which are connected to each other through relationships. Popular examples of RDBMS include Oracle, MySQL, MS SQL Server, IBM DB2, and Microsoft Access. This system helps organize and manage data using rows and columns, making it easier to create, read, update, and delete information, often called CRUD operations. Most modern databases are based on the RDBMS model.
In simple words: RDBMS means Relational Database Management System. It is a way to store data in tables that are linked together, like how you organize information in connected spreadsheets.

🎯 Exam Tip: Focus on explaining what 'Relational' means (data stored in related tables) and list key characteristics like table structure and CRUD operations.

 

Question 2. What is SQL?
Answer: SQL, or Structured Query Language, is a standard programming language used to manage and interact with relational databases. It allows users to perform various tasks such as creating new databases and tables, retrieving specific data, making changes to existing data, and deleting data or database structures. Essentially, SQL is the language that lets people talk to and control their database systems. It is the backbone for data management in many applications.
In simple words: SQL is a special language for databases. It helps you ask questions, add new information, and change things in a database.

🎯 Exam Tip: Define SQL clearly by its full name and its primary purpose: interacting with relational databases through specific commands.

 

Question 3. What are the types of DML?
Answer: Data Manipulation Language (DML) is primarily divided into two types:
1. Procedural DML: This type of DML requires the user to specify not only what data is needed but also exactly how to retrieve or manipulate it. It is more detailed and step-by-step.
2. Non-Procedural DML: With this DML, the user only needs to specify what data they want, without having to explain the specific steps or methods to get it. This makes it simpler and more user-friendly, as the system figures out the "how."
In simple words: DML has two types: Procedural, where you tell the computer both what data you want and how to get it, and Non-Procedural, where you just say what you want, and the computer figures out how.

🎯 Exam Tip: Clearly distinguish between procedural and non-procedural DML by emphasizing whether the "how" (steps) is specified by the user or by the system.

 

Question 4. How can you create the database and work with the database?
Answer: To create and work with a database, you typically use SQL commands in a command prompt or a database management tool. First, you create the database itself using a command like `CREATE DATABASE stud;`. After the database is made, you select it to start working inside it. You use the `USE DATABASE;` command, replacing 'DATABASE' with the name you chose (e.g., `USE stud;`). This tells the system that your next commands are for that specific database. This process makes sure your operations are performed in the correct place.
In simple words: First, you make a database using the `CREATE DATABASE` command. Then, you choose it to work in by typing `USE` followed by the database name.

🎯 Exam Tip: Remember the two key commands: `CREATE DATABASE` to make a database and `USE ` to select it for subsequent operations.

 

Question 5. Write short notes on WAMP?
Answer: WAMP is a popular software package that stands for Windows, Apache, MySQL, and PHP. It provides a complete environment for web development directly on a Windows computer. Apache acts as the web server, MySQL is the database system, and PHP is the programming language used for creating dynamic web content. WAMP is primarily used for local web development and internal testing, allowing developers to build and test websites on their own machines before deploying them online. It simplifies the setup process for many web applications.
In simple words: WAMP is a set of programs for Windows computers that helps you build and test websites easily. It includes Apache for the server, MySQL for the database, and PHP for coding.

🎯 Exam Tip: Know the full form of WAMP and the specific role of each component (Windows OS, Apache web server, MySQL database, PHP scripting language).

 

Question 6. Write a SQL statement to create a table for employees having any five fields and create a table constraint for the employee table. (March 2020)
Answer: Here is a SQL statement to create an `Employee` table with five fields and a table constraint:
`CREATE TABLE Employee(`
`EmpCode CHAR(5) NOT NULL,`
`Name CHAR(20) NOT NULL,`
`Desig CHAR(10),`
`Pay FLOAT CHECK(Pay >= 8000),`
`Allowance FLOAT,`
`PRIMARY KEY (EmpCode, Name)`
`);`
This statement creates a table with an employee code, name, designation, pay, and allowance. It also sets up a primary key using both `EmpCode` and `Name`, and ensures that `Pay` is at least 8000, which are all important for data integrity.
In simple words: You can make an `Employee` table with `EmpCode`, `Name`, `Desig`, `Pay`, and `Allowance`. We also set `EmpCode` and `Name` together as the main ID for each employee and ensure `Pay` is at least 8000.

🎯 Exam Tip: When creating tables, ensure you select appropriate data types, define `NOT NULL` constraints where necessary, and correctly implement `PRIMARY KEY` and `CHECK` constraints.

 

Question 7. Explain DDL commands with suitable examples.
Answer: Data Definition Language (DDL) commands are used to define, modify, and manage the structure of database objects. These commands do not deal with the data itself, but rather with the design of the database. Here are the main DDL commands with examples:
1. CREATE: This command is used to build new database objects like tables, views, or indexes. For example, to create a new `Student` table:
`CREATE TABLE Student (`
`Admno INTEGER NOT NULL PRIMARY KEY,`
`Name CHAR(20) NOT NULL,`
`Gender CHAR(1),`
`Age INTEGER DEFAULT 17,`
`Place CHAR(10)`
`);`
2. ALTER: The `ALTER` command is used to make changes to an existing database object, such as adding a column, deleting a column, or changing a column's data type or size. For instance, to add a new `Address` column to the `Student` table:
`ALTER TABLE Student ADD Address CHAR(25);`
To change the `Address` column to `City` and modify its size:
`ALTER TABLE Student MODIFY Address CHAR(25);`
3. DROP: This command is used to remove an existing database object completely from the database. When you drop a table, all its data and structure are permanently deleted. For example, to remove the `Student` table:
`DROP TABLE Student;`
4. TRUNCATE: The `TRUNCATE` command is used to delete all records from a table, but it keeps the table's structure. It is faster than `DELETE` for removing all rows because it does not log individual row deletions. For instance, to remove all data from the `Student` table:
`TRUNCATE TABLE Student;`
These commands are essential for setting up and maintaining the blueprint of any database.
In simple words: DDL commands help you build and change the structure of your database. `CREATE` makes new tables, `ALTER` changes existing ones, `DROP` deletes them completely, and `TRUNCATE` clears all data from a table while keeping the table structure.

🎯 Exam Tip: Remember that DDL commands affect the *schema* (structure) and are typically irreversible, especially `DROP`. Differentiate `DROP` (deletes structure and data) from `TRUNCATE` (deletes only data, keeps structure).

 

Question 8. Write a note on SQL?
Answer: SQL, or Structured Query Language, is a powerful and widely used standard language for managing and manipulating relational databases. It serves as the primary tool for communicating with databases, allowing users to define database structures, query data, and control access. SQL is a declarative language, meaning you specify *what* you want to achieve rather than *how* to achieve it. Key functions include data definition (creating/modifying tables), data manipulation (inserting/updating/deleting records), and data control (managing user permissions). It is the universal language for database interactions.
In simple words: SQL is a special computer language used to talk to databases. It helps you set up databases, find information, change data, and give or take away access.

🎯 Exam Tip: Emphasize that SQL is a *standard* language for *relational databases* and mention its core functions: defining structure, querying data, and controlling access.

 

Question 9. Write short notes on basic types of DML.
Answer: Data Manipulation Language (DML) is primarily categorized into two basic types:
1. Procedural DML: This type of DML requires users to specify both the exact data they need and the step-by-step process or method to retrieve or modify that data. It involves a detailed procedure for data handling.
2. Non-Procedural DML: In contrast, Non-Procedural DML allows users to simply state what data they want to retrieve or change, without needing to specify the detailed steps or algorithms to accomplish it. The database system then automatically determines the most efficient way to perform the requested operation. SQL is largely a non-procedural DML.
In simple words: DML has two types: Procedural DML, where you tell the computer what to do and how to do it, and Non-Procedural DML, where you only tell it what you want, and it figures out the how.

🎯 Exam Tip: The core distinction between procedural and non-procedural DML lies in the level of detail the user provides regarding the *method* of data manipulation.

 

Question 10. Explain DML commands with suitable examples.
Answer: Data Manipulation Language (DML) commands are used to manage and change data within the database tables. They allow users to retrieve, insert, update, and delete records. Here are the main DML commands with examples:
1. INSERT: This command is used to add new rows of data into a table. It lets you populate your tables with information.
Syntax: `INSERT INTO [column-list] VALUES (values);`
Example: `INSERT INTO Student VALUES(108, 'Jisha', 'F', 19, 'Delhi');`
2. DELETE: The `DELETE` command is used to remove one or more rows from a table based on a specified condition. If no condition is given, all rows are deleted. This command also frees up the space occupied by the deleted records.
Syntax: `DELETE FROM WHERE condition;`
Example: `DELETE FROM Employee WHERE DESIG='Mechanic';`
3. UPDATE: This command is used to modify existing data within a table. You can change the values in one or more columns for rows that meet a specific condition.
Syntax: `UPDATE SET column-name = value, column-name = value,... WHERE condition;`
Example: `UPDATE Student SET Name = 'Mini' WHERE Admno=105;`
These commands are fundamental for managing the actual content stored in a database.
In simple words: DML commands help you work with the data inside tables. `INSERT` adds new data, `DELETE` removes data, and `UPDATE` changes existing data.

🎯 Exam Tip: Always specify a `WHERE` clause with `DELETE` and `UPDATE` commands to avoid unintended changes to all rows in the table.

 

Question 11. What are the various processing skills of SQL?
Answer: SQL (Structured Query Language) has several processing skills or capabilities that allow it to manage and interact with databases effectively. These skills can be categorized as:
1. Data Definition Language (DDL): SQL's DDL commands, such as `CREATE`, `ALTER`, and `DROP`, are used for defining and modifying the structure (schema) of relations (tables), deleting relations, creating indexes, and changing relation schemas.
2. Data Manipulation Language (DML): SQL's DML includes commands like `INSERT`, `DELETE`, and `UPDATE` which are used to add, remove, and modify tuples (rows) in the database.
3. Embedded Data Manipulation Language: This refers to the ability to embed SQL commands within high-level programming languages, allowing applications to interact with databases.
4. View Definition: SQL includes commands that allow users to define "views" of tables. A view is a virtual table based on the result-set of an SQL statement, providing a simplified or restricted representation of the underlying data.
5. Authorization: SQL offers commands to manage access rights and permissions for different users or roles, controlling who can perform specific actions on relations and views.
6. Integrity: SQL provides features for integrity checking using various conditions and constraints (like `PRIMARY KEY`, `FOREIGN KEY`, `CHECK`, `NOT NULL`) to ensure data accuracy and consistency.
7. Transaction Control: SQL includes commands (like `COMMIT`, `ROLLBACK`, `SAVEPOINT`) for managing database transactions, which are sequences of operations performed as a single logical unit of work, ensuring data consistency.
These combined capabilities make SQL a comprehensive language for database management.
In simple words: SQL can do many things, like building and changing database structures (DDL), adding and changing data (DML), letting other programs use it, creating special views of data, controlling who can see or change data, keeping data correct, and managing groups of changes at once.

🎯 Exam Tip: For this question, list the main categories (DDL, DML, DCL, TCL, Views, Integrity, Authorization) and give a brief function for each, possibly mentioning one or two commands as examples.

 

Question 12. Write short notes on DCL (Data Control Language).
Answer: Data Control Language (DCL) is a part of SQL that deals with controlling access and permissions to the database. It is primarily used for authorization, ensuring that only authorized users can perform specific operations. The main commands in DCL are:
• GRANT: This command is used to give specific permissions to one or more users. For example, a database administrator can grant a user permission to select (read) data from a table, or to insert new data.
• REVOKE: This command is used to withdraw or remove permissions that were previously granted to users. For instance, if a user no longer needs access to a particular table, their permissions can be revoked.
DCL is crucial for maintaining database security and controlling data integrity by restricting who can perform various operations. It helps prevent unauthorized access and data corruption.
In simple words: DCL (Data Control Language) is used to control who can do what in a database. `GRANT` gives people access or permissions, and `REVOKE` takes those permissions away.

🎯 Exam Tip: Remember DCL's primary role is *security and access control*. Focus on `GRANT` for giving permissions and `REVOKE` for taking them away.

 

Question 13. Write short notes on Data Query Language (DQL).
Answer: Data Query Language (DQL) is a component of SQL that focuses on retrieving data from a database. Its primary purpose is to allow users to ask questions or "query" the database to get specific information. The most well-known and almost exclusive command in DQL is `SELECT`. The `SELECT` statement is used to fetch data from one or more tables based on various criteria. For example, you can select all columns from a table, or only specific columns, and filter the results using conditions. DQL is crucial for analyzing data and extracting meaningful insights, without making any changes to the data itself.
In simple words: DQL, or Data Query Language, is for asking the database to show you information. The main command is `SELECT`, which helps you pick out the data you want to see.

🎯 Exam Tip: Understand that DQL is essentially about *reading* data, and `SELECT` is the fundamental command for all data retrieval operations.

 

Question 14. How will you retain duplicate rows of a table?
Answer: To retain and display duplicate rows in a table, you use the `ALL` keyword along with the `SELECT` statement. By default, when you use `SELECT` without `DISTINCT` or `ALL`, some database systems might remove duplicate rows in the result set. However, explicitly using `ALL` ensures that every single row, including any duplicates, is shown in the output, just as it exists in the original table. This is useful when you want to see the complete, raw data without any filtering for unique values.
Example: `SELECT ALL Place FROM Student;`
In simple words: To show all rows in a table, even if some are exactly the same, you use the `ALL` keyword with the `SELECT` command. This tells the database to display everything, including copies.

🎯 Exam Tip: Remember that `ALL` (explicitly stating to keep all rows, including duplicates) is the opposite of `DISTINCT` (which filters out duplicates from the result set).

 

Question 15. What are the functions performed by DDL?
Answer: Data Definition Language (DDL) performs several key functions related to the structure and organization of a database. These functions include:
1. Identifying Data Division: DDL helps to identify and define different types of data divisions, such as data items, segments, records, and database files, establishing how information is structured.
2. Unique Naming: It gives a unique name to each data item type, record type, file type, and the entire database, which is crucial for organization and retrieval.
3. Specifying Data Types: DDL ensures that each data item has a proper data type (e.g., integer, character, float), which dictates what kind of data can be stored.
4. Defining Size: It is used to define the specific size for each data item, ensuring that enough space is allocated and data fits correctly.
5. Defining Value Range: DDL can define the acceptable range of values for a data item, adding a layer of data validation and integrity.
6. Specifying Privacy Locks: It allows for the specification of privacy locks to prevent unauthorized data entry or access, enhancing database security.
These functions collectively enable the creation and maintenance of a robust and well-defined database schema.
In simple words: DDL commands help you set up and manage the basic design of a database. They define things like what type of data goes where, how big it can be, its name, and who can access it.

🎯 Exam Tip: When listing DDL functions, think about everything involved in *creating the blueprint* of a database – from naming conventions to data types and security settings.

 

Question 16. Differentiate IN and NOT IN keywords.
Answer: The `IN` and `NOT IN` keywords are used in SQL queries to filter data based on a list of specified values. They act as shortcuts for multiple `OR` conditions.
**IN Keyword:**
• The `IN` keyword is used to specify a list of values. It helps you find records where a column's value matches *any* value within that list. It is similar to using multiple `OR` conditions. For example, `WHERE City IN ('Delhi', 'Mumbai')` would find records where the City is either Delhi OR Mumbai.
**NOT IN Keyword:**
• The `NOT IN` keyword is the opposite of `IN`. It displays only those records where a column's value *does not* match any value in the specified list. For example, `WHERE City NOT IN ('Delhi', 'Mumbai')` would find records where the City is neither Delhi NOR Mumbai.
Both keywords are useful for simplifying `WHERE` clauses when you need to check against multiple possibilities or exclusions, making queries more readable and efficient than long chains of `OR` or `AND` conditions.
In simple words: `IN` helps you find data that matches *any* item in a list. `NOT IN` helps you find data that *does not match* any item in a list.

🎯 Exam Tip: Think of `IN` as "is one of these" and `NOT IN` as "is not one of these". Be careful with `NULL` values when using `NOT IN`, as it can lead to unexpected results.

 

Question 17. Explain Primary Key Constraint with suitable examples.
Answer: A Primary Key constraint is a crucial rule in databases that uniquely identifies each record (row) in a table. It ensures that every record has a unique identifier, and it does not allow `NULL` (empty) values. This means each record can be distinctly identified. The Primary Key is similar to a Unique constraint, but a table can only have one Primary Key. This key is used to establish relationships between different tables.
Example: Consider creating a `Student` table. The `Admno` (Admission Number) is a good candidate for a Primary Key because each student has a unique admission number.
`CREATE TABLE Student (`
`Admno INTEGER NOT NULL PRIMARY KEY,`
`Name CHAR(20) NOT NULL,`
`Gender CHAR(1),`
`Age INTEGER,`
`Place CHAR(10)`
`);`
In this example, `Admno` is set as the `PRIMARY KEY`. This guarantees that no two students can have the same `Admno`, and every student record must have an `Admno` (it cannot be empty). This uniqueness helps link student data to other tables, like their grades or courses.
In simple words: A Primary Key is a special column in a table that gives each row a unique ID, like an employee ID. It must always have a value and cannot be repeated for different rows.

🎯 Exam Tip: Remember two key properties of a Primary Key: it must be `UNIQUE` (no duplicates) and `NOT NULL` (cannot be empty). This ensures every row is uniquely identifiable.

 

Question 18. Explain GROUP BY and HAVING clauses.
Answer: The `GROUP BY` and `HAVING` clauses are used together in SQL to organize and filter data, especially when dealing with summary calculations.
**GROUP BY Clause:**
The `GROUP BY` clause is used with the `SELECT` statement to group rows that have the same values in one or more columns into a summary group. This is typically used with aggregate functions (like `COUNT`, `SUM`, `AVG`, `MAX`, `MIN`) to perform calculations on each group. For instance, you could group students by gender to count how many males and females there are.
Syntax: `SELECT FROM GROUP BY ;`
Example: To count students by Gender from the `Student` table:
`SELECT Gender, COUNT(*) FROM Student GROUP BY Gender;`
**HAVING Clause:**
The `HAVING` clause is used to filter the groups created by the `GROUP BY` clause. While `WHERE` filters individual rows, `HAVING` filters entire groups based on a condition, often involving aggregate functions. This means you can set conditions on the results of `COUNT`, `SUM`, etc.
Syntax: `SELECT FROM GROUP BY HAVING condition;`
Example: To count the number of Male and Female students only from 'Chennai':
`SELECT Gender, COUNT(*) FROM Student GROUP BY Gender HAVING Place = 'Chennai';`
In essence, `GROUP BY` collects identical data into groups, and `HAVING` then applies conditions to these summarized groups.
In simple words: `GROUP BY` collects similar rows into groups, like grouping students by their city. `HAVING` then filters these groups based on a condition, for example, showing only cities that have more than 10 students.

🎯 Exam Tip: The main difference is that `WHERE` filters *rows* before grouping, and `HAVING` filters *groups* after grouping. Aggregate functions can only be used in the `HAVING` clause, not `WHERE`.

 

Question 19. List the data types used in SQL.
Answer: SQL supports various data types to store different kinds of information in database tables. Choosing the correct data type is important for efficiency and data integrity. Common data types include:
• `CHAR` (Character): Stores fixed-length string data. For example, `CHAR(10)` will always store 10 characters, padding with spaces if needed.
• `VARCHAR`: Stores variable-length string data. For instance, `VARCHAR(20)` can store anywhere from 0 to 20 characters, saving space.
• `DEC` (Decimal): Stores exact decimal numbers, suitable for financial data where precision is critical.
• `NUMERIC`: Similar to `DEC`, stores exact numeric values.
• `INT` (Integer): Stores whole numbers without any decimal places.
• `SMALLINT`: Stores smaller whole numbers, typically using less storage space than `INT`.
• `FLOAT`: Stores approximate floating-point numbers, used for numbers with decimal points where precision is less critical.
• `REAL`: Similar to `FLOAT`, but typically stores single-precision floating-point numbers.
• `DOUBLE`: Stores double-precision floating-point numbers, offering higher precision than `FLOAT` or `REAL`.
Other common types might include `DATE`, `TIME`, `DATETIME`, and `BOOLEAN`, depending on the specific SQL database system.
In simple words: SQL has different kinds of data types for storing various information, like `CHAR` and `VARCHAR` for text, `INT` for whole numbers, `FLOAT` for numbers with decimals, and `DECIMAL` for exact money values.

🎯 Exam Tip: When listing data types, mention categories like strings (CHAR, VARCHAR), exact numbers (DEC, NUMERIC, INT), and approximate numbers (FLOAT, REAL, DOUBLE).

 

Question 20. Write notes on a predetermined set of commands of SQL?
Answer: SQL uses a set of specific commands to work with databases. These commands are grouped into categories like Keywords, Commands, Clauses, and Arguments, each with its own role in building queries. Understanding these helps in efficient database management.

• **Keywords:** These are special words in SQL that have a fixed meaning. They are understood as instructions for the database.
• **Commands:** These are instructions given by the user to the database, also known as statements.
• **Clauses:** These parts typically begin with a keyword and are followed by arguments to form a complete instruction.
• **Arguments:** These are the values or parameters provided to complete a clause.

In simple words: SQL has special words called keywords, instructions called commands, parts called clauses, and values called arguments. These all work together to help us talk to the database.

🎯 Exam Tip: When explaining SQL commands, always list the main categories (Keywords, Commands, Clauses, Arguments) and give a brief definition for each.

 

Question 21. Explain how to set a primary key for more than one field? Explain with example.
Answer:A primary key can be set for more than one field by using a **Table Constraint**. This is when a constraint is applied to a group of fields within a table's definition, typically at the end. This allows the combination of these multiple columns to uniquely identify each record in the table, rather than a single column doing so.
**Example:**

CREATE TABLE Student1:
(
    Admno integer NOT NULL,
    Firstname char (20),
    Lastname char(20),
    Gender char(1),
    Age integer,
    Place char(10),
    PRIMARY KEY (Firstname, Lastname) -- This is the table constraint
);

In this example, the 'Firstname' and 'Lastname' fields together form the Primary Key. This means that the combination of a student's first name and last name must be unique for each record. This ensures no two students can have the exact same first name and last name in this table, providing a strong unique identifier.
In simple words: To make a primary key using more than one field, you use a 'Table Constraint'. This means you pick two or more columns, like 'Firstname' and 'Lastname', and say that their combination must be unique for each row. So, no two students can have the same first name and last name.

🎯 Exam Tip: Remember that a primary key can be made of one or more columns, but its purpose is always to uniquely identify each row in the table.

 

Question 22. Explain how to generate queries and retrieve data from the table? Explain?
Answer: A query is a command given to a database to get a desired result from a table. The `SELECT` command is mainly used to query or retrieve data from one or more tables in the database. It can be used in various forms to retrieve a subset of records. This command is fundamental for extracting information.
**Syntax:** `SELECT < column-list > FROM < table-name > ;`
**Explanation:**

• **Table-name:** This is the name of the table from which you want to retrieve information.
• **Column-list:** This includes one or more column names from which data will be retrieved.

In simple words: To get information from a database, you use a 'query'. The most common way to do this is with the `SELECT` command. You tell it which columns you want to see and which table to get them from.

🎯 Exam Tip: Understand the basic `SELECT` syntax, including the `FROM` clause, as it is the most frequently used SQL command for data retrieval.

III. Answer the Following Questions (5 Marks)

 

Question 1. Write the processing skills of SQL.
Answer: SQL has several processing skills that allow users to manage and interact with databases effectively. These skills cover various aspects of database operations, making SQL a powerful language.

1. **Data Definition Language (DDL):** DDL commands help define the structure of a database. This includes tasks like creating new tables, deleting existing relationships, setting up indexes, and modifying table schemas.
2. **Data Manipulation Language (DML):** DML commands are used to manage the data within database tables. They allow users to insert new rows (tuples), delete existing ones, and modify or update data.
3. **Embedded Data Manipulation Language:** This refers to a form of SQL that is used within other high-level programming languages. It allows application programs to interact directly with databases.
4. **View Definition:** SQL includes commands for defining views. Views are like virtual tables based on the result of an SQL query, providing a simplified or restricted view of the underlying data.
5. **Authorization:** SQL provides commands to manage access rights. These commands control who can access what data and what operations they can perform on relations and views.
6. **Integrity:** SQL offers ways to ensure data integrity by using various conditions. These conditions help maintain the accuracy and consistency of data within the database.
7. **Transaction Control:** Transaction control commands manage database transactions. They help ensure data consistency and reliability, especially during complex operations involving multiple changes.

In simple words: SQL can do many things, like defining how the database is built (DDL), changing the data inside (DML), using SQL within other programs, making special 'views' of data, setting who can see what (authorization), keeping data correct (integrity), and managing changes (transaction control).

🎯 Exam Tip: Memorize the main categories of SQL commands (DDL, DML, DQL, DCL, TCL) and their primary functions. Providing clear, concise definitions for each is key.

 

Question 2. Explain ALTER command in detail.
Answer: The `ALTER` command in SQL is part of the Data Definition Language (DDL) and is used to change the structure or schema of an existing table. It offers flexibility to modify a table after it has been created without losing existing data.
Here's how the `ALTER` command is used for different operations:

• **To add a new column:**
  • **Syntax:** `ALTER TABLE ADD [ ];`
  • **Example:** `ALTER TABLE Student ADD Address char;` (This adds a new column named 'Address' with character data type to the 'Student' table.)
• **To modify an existing column:**
  • **Syntax:** `ALTER TABLE MODIFY ;`
  • **Example:** `ALTER TABLE Student MODIFY Address char(25);` (This changes the data type and size of the 'Address' column in the 'Student' table to character with a size of 25.)
• **To rename an existing column:**
  • **Syntax:** `ALTER TABLE RENAME COLUMN TO ;`
  • **Example:** `ALTER TABLE Student RENAME COLUMN Address TO City;` (This renames the 'Address' column to 'City' in the 'Student' table.)
• **To remove a column:**
  • **Syntax:** `ALTER TABLE DROP COLUMN ;`
  • **Example:** `ALTER TABLE Student DROP COLUMN City;` (This deletes the 'City' column from the 'Student' table.)

The `ALTER` command is crucial for evolving database schemas as requirements change.
In simple words: The `ALTER` command is used to change how an existing table is built. You can use it to add new columns, change the type or size of an existing column, rename a column, or even remove a column.

🎯 Exam Tip: When asked to explain the `ALTER` command, always provide clear examples for adding, modifying, renaming, and dropping columns to demonstrate a full understanding of its capabilities.