Unit 1: Database Concepts
- SQL & SQL Commands, DDL Commands, Constraints & DML Commands
- Database Creation
- Table Creation
- Inserting Records, Deleting Records, Updating Records
- Modify Table Structure
Important Question Answer
SQL TABLE CREATION, CONSTRAINT AND MANIPULATION
1. What is SQL?
Answer: SQL is a language, that is used to manage data stored in RDBMS. It uses tables to manipulate and retrieve information from databases for analysis.
2. What are the advantages of SQL?
Answer: Advantages of SQL are –
- High Speed
- No coding needed
- Well defined standards
- Interactive Language
- Multiple data views
3. What can you do with the help of SQL Commands?
Answer: By using SQL commands, one can search for any data in the database and perform other functions like creating tables, adding records, modifying data, removing rows, dropping tables, etc.
4. How many types of SQL Commands?
Answer: There are five types of SQL commands:-
- DDL (Data Definition Language) – CREATE, DROP, ALTER, TRUNCATE
- DML (Data Manipulation Language): – INSERT, UPDATE, DELETE
- DQL (Data Query Language): – SELECT
- DCL (Data Control Language): – GRANT, REVOKE
- TCL (Transaction Control Language):- COMMIT, ROLLBACK, SAVEPOINT
Note: In some books, the SELECT command is mentioned under the category of DML command.
5. What is DDL?
Answer: Data Definition Language (DDL) is a language command that can be used to define the database schema. It is used to create and modify the structure and constraints of the database.
DDL is a set of SQL commands used to create, modify, and delete database structures but not
6. Write the list of the DDL commands?
Answer: List of the DDL Commands:
- CREATE – to create the database, table, index, views, etc.
- DROP – to drop/remove the database, table, index, view, etc.
- ALTER – to alter/modify the structure of the database.
- TRUNCATE – to remove all records from a table.
- RENAME – to rename the table, views, index, etc.
7. What is DML?
Answer: The SQL commands that deal with the manipulation of data present in the table (database) are known as DML or Data Manipulation Language commands.
Data Manipulation Language (DML) command is used to insert, modify and delete data in a database.
8. Write the list of the DML commands?
Answer: The List of DML commands is:-
- INSERT – to insert a record into a table.
- UPDATE – to update existing data within a table.
- DELETE – to delete records from a database table.
9. Differentiate between DDL & DML Commands?
Answer: DDL vs DML
|DDL (Data Definition Language)||DML (Data Manipulation Language)|
|(i) DDL stands for Data Definition Language.||(i) DML stands for Data Manipulation Language|
|(ii) It is used to create database schema and can be used to|
define some constraints as
|(ii) It is used to Insert, update or delete the data.|
|(iii) It basically defines the|
column (Attributes) of the
|(iii) It adds or updates the row of the table. These rows are|
called as a tuple.
10. What is Data Type?
Answer: Data Type specifies the types of data that can be stored in the field.
11. Write the name of the five most commonly used data types in MySQL server / SQL.
Answer: Most commonly used data types are-
- Text Data Type: – CHAR(size), VARCHAR(size),
- Numeric Data Type: -INTEGER(size), DECIMAL(M,D), FLOAT(size), REAL,
- Date Data Type:- DATE, and TIME
12. What is the CHAR Data Type?
Answer: CHAR(n) is a fixed-length character string.
‘n’ is the number of characters.
Example: “Vijay Kumar”
13. What is the VARCHAR Data Type?
Answer: VARCHAR(n) is a Variable-length character string. ‘n’ is the maximum number of characters in the string.
Example: “Vijay Kumar”, “Ashok Sen”
14. What is DATE Data Type?
Answer: DATE data type allows to store the date value in the form of ‘YYYY-MM-DD’.
15. What is INTEGER Data Type?
Answer: INTEGER data type allow to insert of integer value without decimal points.
Example: 25, 269869
16. What is DECIMAL(m, d) Data Type?
Answer: DECIMAL(m, d) allows to store of decimal point numeric values.
Fixed point number m represents the number of significant digits that are stored for values and d represents the number of digits that can be stored following the decimal point.
If d is zero or not specified then the value does not contain any decimal part.
Example: DECIMAL(5,2) : 999.99, -567.78
DECIMAL (5) : 23456, 99999
17. Differentiate between CHAR and VARCHAR.
Answer: The difference between CHAR(n) and VARCHAR(n) :
- CHAR(N) Datatype:
- The CHAR datatype is a fixed-length character string.
- When a column is given datatype as CHAR(N), then MySQL / SQL ensures that all values stored in that column have this length i.e. N bytes.
- If a value is shorter than this length N then blanks are added, but the size of the value remains n bytes.
- VARCHAR(N) Datatype:
- The VARCHAR is a variable-length character string.
- When a column is given the datatype VARCHAR(N), then the maximum size a value in this column can have is N bytes.
- Each value that is stored in this column stores exactly as you specify it i.e., no blanks are added if the length is shorter than the maximum length N.
- If you exceed the maximum length of N, then an error message is displayed.
18. Differentiate between INTEGER and DECIMAL.
Answer: The difference between INTEGER and DECIMAL: INTEGER datatype allows one to enter a numeric value without a decimal point while DECIMAL datatype allows entering a numeric value with a decimal point.
19. Which command is used to create a database in SQL? Write their Syntax.
Answer: CREATE DATABASE command is used to create a database in SQL.
CREATE DATABASE [IF NOT EXISTS] <DBNAME>;
Where [ ] is optional.
IF NOT EXISTS is an optional clause, which tells the SQL server, to create a database if that given database does not exist.
20. Write a command to create a database named “school”.
Answer: CREATE DATABASE school;
CREATE DATABASE IF NOT EXISTS school;
21. Write a command to open a database / change your working database to “school”.
Answer: USE school;
You will get a message from SQL “Database changed”
22. Which command in SQL list all the database?
Answer: SHOW DATABASES;
It will display the list of databases available in SQL.
23. Which command in SQL deletes a database?
Answer: DROP DATABASE <dbname>;
DROP DATABASE with database name command is used to remove the database and all its tables, views, and indexes.
Note: SQL will not show any warning message before deleting the database.
24. Write a command in SQL to delete a database “school”?
Answer: DROP DATABASE school;
25. Identify the error in the given SQL command. Write the correct command also.
Answer: Missing keyword DATABASE. In the above command database name is “DATABASE_test”.
The correct command is
CREATE DATABASE DATABASE_test;
CREATE DATABASE test;