1) What is SQL or Structured Query Language?
SQL is a Structured Query Language used to communicate with database. A database is the one which will have logical data storage structure called tables. In order to retrieve data from it and store/update data in it, we need some language which can do the needful. This is provided by SQL. It supports operations like insertion, updation, retrieval and deletion.
2) Explain Relational Database Management System (RDBMS)?
RDBMS is a relational database management system where datas are managed in tables. Each tables in DB are inter-related by means of relationship or mappings. It is established using primary and foreign key constraints.
RDBMS is a program that guides us how to create and maintain a database. It tells us how to divide related information into different tables and inter-relate them so that we can select/insert/update/delete all the related data easily and efficiently.
3) Explain the properties of a relational table?
Below are the properties of RDBMS:
- Should be able to store all kinds of data that exists in this real world. Since we need to work with all kinds of data and requirements, database should be strong enough to store all kinds of data that is present around us.
- Should be able to relate the entities / tables in the database by means of a relation. i.e.; any two tables should be related. Let us say, an employee works for a department. This implies that Employee is related to a particular department. We should be able to define such a relationship between any two entities in the database. There should not be any table lying without any mapping.
- Data and application should be isolated. Because database is a system which gives the platform to store the data, and the data is the one which allows the database to work. Hence there should be clear differentiation between them.
- There should not be any duplication of data in the database. Data should be stored in such a way that it should not be repeated in multiple tables. If repeated, it would be unnecessary waste of DB space and maintaining such data becomes chaos.
- DBMS has a strong query language. Once the database is designed, this helps the user to retrieve and manipulate the data. If a particular user wants to see any specific data, he can apply as many filtering conditions that he wants and pull the data that he needs.
- Multiple users should be able to access the same database, without affecting the other user. i.e.; if teachers want to update a student’s marks in Results table at the same time, then they should be allowed to update the marks for their subjects, without modifying other subject marks. A good database should support this feature.
- It supports multiple views to the user, depending on his role. In a school database, Students will able to see only their reports and their access would be read only. At the same time teachers will have access to all the students with the modification rights. But the database is the same. Hence a single database provides different views to different users.
- Database should also provide security, i.e.; when there are multiple users are accessing the database, each user will have their own levels of rights to see the database. Some of them will be allowed to see whole database, and some will have only partial rights. For example, instructor who is teaching Physics will have access to see and update marks of his subject. He will not have access for other subjects. But the HOD will have full access on all the subjects.
Database should also support ACID property. i.e.; while performing any transactions like insert, update and delete, database makes sure that the real purpose of the data is not lost.
4) What is ACID mean in Sql Server?
ACID is used for evaluating application and database architecture. Below are the ACID properties
- Atomicity : This property states that each transaction must be considered as a single unit and must be completed fully or not completed at all. No transaction in the database is left half completed. Database should be in a state either before the transaction execution or after the transaction execution. It should not be in a state ‘executing’.
- Consistency : Any transaction should not inject any incorrect or unwanted data into the database. It should maintain the consistency of the database.
- Isolation : If there are multiple transactions executing simultaneously, then all the transaction should be processed as if they are single transaction. But individual transaction in it should not alter or affect the other transaction. That means each transaction should be executed as if they are independent.
- Durability : The database should be strong enough to handle any system failure. It should not be working for single transaction alone. It should be able to handle multiple transactions too. If there is any set of insert /update, then it should be able to handle and commit to the database. If there is any failure, the database should be able to recover it to the consistent state.
5) What are the difference between “Where” and “Having” clause in Sql Server?
“Where” clause is used to filter the rows based on condition. Even though “Having” clause used with SELECT clause, this is mainly used with GROUP BY clause. If GROUP BY clause not used then “HAVING” clause works like a “WHERE” clause.
6) Explain primary key in Sql Server?
This constraint is another type of UNIQUE constraint. This constraint forces the column to have unique value and using which, we can uniquely determine each row. This column cannot be NULL.
7) Explain unique key in Sql Server?
It is a constraint which enforces only the unique value in the column of the table. Unlike primary key, it will allow one row column to be NULL. But not more than one record’s column value can be NULL.
8) Explain foreign key in Sql Server?
Foreign key is used to establish a relationship between the columns of other table. Foreign key is the one to be created between two tables by referencing primary key of one table as foreign key in another table.
9) What is the use of “JOIN” in Sql Server?
It is the method of retrieving related data from more than one table using their parent –child keys. That means related tables can be joined using primary-foreign keys to get the meaningful result.
10) Explain the types of JOINS in Sql Server?
Below is the list of JOINS in Sql Server –
- Inner Join : This is the most common type of join and is similar to AND operation. It combines the results of one or more tables and displays the results when all the filter conditions are met. There are two ways to represent the same
- Right Outer Join : This join is opposite of left outer join. It retrieves all the records from the table which is on the RHS of ‘RIGHT OUTER JOIN’ clause and only the matching records from LHS.
- Left Outer Join : This join retrieves all the records from the table which is on the LHS of ‘LEFT OUTER JOIN’ clause and only the matching records from RHS.
- Full Outer Join : This is combination of both left and outer join. It displays all the matching columns from both the tables, and if it does not find any matching row, it displays NULL.
11) In which TCP/IP port does Sql Server run?
By default it runs on port – 1433 and it can be changed from “Network Utility TCP/IP” properties.