Question:- What are the applications of SQL?
Answer:- The major applications of SQL include: • Writing data integration scripts • Setting and running analytical queries • Retrieving subsets of information within a database for analytics applications and transaction processing • Adding, updating, and deleting rows and columns of data in a database
Question:- What is a DEFAULT constraint?
Answer:- Constraints in SQL are used to specify some sort of rules for processing data and limiting the type of data that can go into a table. Now, let us understand what is a default constraint. A default constraint is used to define a default value for a column so that it is added to all new records if no other value is specified. For example, if we assign a default constraint for the E_salary column in the following table and set the default value to 85000, then all the entries of this column will have the default value of 85000, unless no other value has been assigned during the insertion.
Question:- What is a UNIQUE constraint?
Answer:- Unique constraints ensure that all the values in a column are different. For example, if we assign a unique constraint to the e_name column in the following table, then every entry in this column should have a unique value. First, we will create a table. create table stu2(s_id int unique, s_name varchar(20)) Now, we will insert the records. insert into stu2 values(1,’Julia’) insert into stu2 values(2,’Matt’) insert into stu2 values(3,’Anne’) A PRIMARY KEY constraint will automatically have a UNIQUE constraint. However, unlike a PRIMARY KEY, multiple UNIQUE constraints are allowed per table.
Question:- What is meant by table and field in SQL?
Answer:- An organized data in the form of rows and columns is said to be a table. Simply put, it is a collection of related data in a table format. Here rows and columns are referred to as tuples and attributes, and the number of columns in a table is referred to as a field. In the record, fields represent the characteristics and attributes and contain specific information about the data.
Question:- What is a primary key?
Answer:- A primary key is used to uniquely identify all table records. It cannot have NULL values and must contain unique values. Only one primary key can exist in one table, and it may have single or multiple fields, making it a composite key. Now, we will write a query for demonstrating the use of a primary key for the employee table: // CREATE TABLE Employee ( ID int NOT NULL, Employee_name varchar(255) NOT NULL, Employee_designation varchar(255), Employee_Age int, PRIMARY KEY (ID) );
Question:- What is a unique key?
Answer:- The key that can accept only a null value and cannot accept duplicate values is called a unique key. The role of a unique key is to make sure that all columns and rows are unique. The syntax for a unique key will be the same as the primary key. So, the query using a unique key for the employee table will be: // CREATE TABLE Employee ( ID int NOT NULL, Employee_name varchar(255) NOT NULL, Employee_designation varchar(255), Employee_Age int, UNIQUE(ID) );
Question:- What is the difference between primary key and unique key?
Answer:- Both primary and unique keys carry unique values but a primary key cannot have a null value, while a unique key can. In a table, there cannot be more than one primary key, but there can be multiple unique keys.
Question:- What is a foreign key?
Answer:- A foreign key is an attribute or a set of attributes that reference the primary key of some other table. Basically, a foreign key is used to link together two tables. Let us create a foreign key for the following table: CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) )
Question:- What are the subsets of SQL?
Answer:- The main subsets of SQL are: • Data Definition Language (DDL) • Data Manipulation Language (DML) • Data Control Language (DCL) • Transaction Control Language (TCL)
Question:- Explain the different types of SQL commands.
Answer:- • DDL: DDL is that part of SQL that defines the data structure of the database in the initial stage when the database is about to be created. It is mainly used to create and restructure database objects. Commands in DDL are: • Create table • Alter table • Drop table • DML: DML is used to manipulate already existing data in a database, i.e., it helps users to retrieve and manipulate data. It is used to perform operations such as inserting data into the database through the insert command, updating data with the update command, and deleting data from the database through the delete command. • DCL: DCL is used to control access to the data in the database. DCL commands are normally used to create objects related to user access and to control the distribution of privileges among users. The commands that are used in DCL are Grant and Revoke. • TCL: TCL is used to control the changes made by DML commands. It also authorizes the statements to assemble in conjunction with logical transactions. The commands that are used in TCL are Commit, Rollback, Savepoint, Begin, and Transaction.
Question:- What are the usages of SQL?
Answer:- The following operations can be performed by using SQL database: • Creating new databases • Inserting new data • Deleting existing data • Updating records • Retrieving the data • Creating and dropping tables • Creating functions and views • Converting data types
Question:- What is an index?
Answer:- Indexes help speed up searching in a database. If there is no index on a column in the WHERE clause, then the SQL Server has to skim through the entire table and check each and every row to find matches, which may result in slow operations in large data. Indexes are used to find all rows matching with some columns and then to skim through only those subsets of the data to find the matches. Syntax: CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN)
Question:- Explain the types of indexes.
Answer:- Single-column Indexes: A single-column index is created for only one column of a table. Syntax: CREATE INDEX index_name ON table_name(column_name); Composite-column Indexes: A composite-column index is created for two or more columns of a table. Syntax: CREATE INDEX index_name ON table_name (column1, column2) Unique Indexes: A unique index is used for maintaining the data integrity of a table. A unique index does not allow multiple values to be inserted into the table. Syntax: CREATE UNIQUE INDEX index ON table_name(column_name)
Question:- What are entities and relationships?
Answer:- Entities: An entity can be a person, place, thing, or any identifiable object for which data can be stored in a database. For example, in a company’s database, employees, projects, salaries, etc., can be referred to as entities. Relationships: A relationship between entities can be referred to as a connection between two tables or entities. For example, in a college database, the student entity and the department entities are associated with each other. That ends the section of basic interview questions. Let us now move on to the next section of intermediate interview questions.