|It stores the data in hierarchical form||It stored the data in tabular form|
|At a time only ONE user can manage||At a time MULTIPLE user can manage|
|It requires less software and hardware capacity||It requires more software and hardware capacity|
|It is capable of managing limited amount of data||It is capable of managing unlimited amount of data|
|It does not support distributed database||It supports distributed database|
|It does not provides ACID(Atomicity, Consistency, Isolation, and Durability) model||It supports stucture of data on ACID model|
|SQL is a query language user for operating relational databases||MySQL is a first open-source relational database|
|SQL is a query language||MySQL is a relational database which uses sql as a query language|
|SQL used to access, update and manipulate stored data from the database||MySQL is used to store the data into database in well organized manner|
|SQL used to writing queries to retrieve data from databases||MySQL used to store, modifying data in tabular format|
|SQL is just a query language so does not support any interface||MySQL comes with tools which provides UI for building databases.|
|SQL is a standard format which follows simple syntax and commands||MySQL get updated frequently|
|SQL support single storage engine||MySQL support more storage engines, so it is more flexible|
|SQL is not an open-source so if you get any issue you have to rely on Microsoft SQL Server support||MySQL is an open-source platform and it has wide and robust support from community|
|SQL is much more secure||MySQL is less secure compared to SQL, Any one can easily modify the database files.|
SQL Constraints are used to define rules into the data table. It means we can define some rules for the table that what should be the checks must be performed while inserting the data into table.
This constraints cab be ensure the reliability and accuracy of the data into the table. Suppose we want dateofbirth for each student then we can set a constraints on DateOfBirth column as not null, so it will enforce user to enter the dateofbirth else it will not allow user to make the student entries into the student table. In this way user has to enter the dateofbirth for the student while entering data to the table.
SQL supports multiple types of constraints as below
The unique constratints ensures that all the values in a column are different
PrimaryKey and UNIQUE key constratints both are same and work in a same way, but in a table we can put more than 1 unique key while we can keep only 1 PrimaryKey constratints, UNIQUE Key constratint can have 1 null value while primary key column can not have any null value.
The below example will creates an unique constratint on the pk column in student table
CREATE TABLE Student (
PK int NOT NULL UNIQUE,
FirstName varchar(100) NOT NULL,
In SQL Server foreign key constraint is a key which is used to link two tables together. Foreign key is a field in one table that relates to primary key of another table. The table which contains PRIMARY key that is called parent table and the table which contains reference key is called child table.
A jon claused is used to combine data or raws from more than 1 table based on some common field value. Below are the different types of the JOINs in SQL:
The SQL SELF JOIN is a regular join but in this case it will join with itself rather than another table.
SQL Cross join is also a join but it will produced a result set equals to number of rows from first table multiplied by number of rows from another table if no where condition is provided. This result set is also knows as Cartesian Product. If we add where condition then it will work similar to INNER JOIN.
Indexes are special data structure associated with views or tables which helps query to speed up. It means where we are running query on very large table then indexes helps us to return the result faster. There are two types of indexed in the SQL Server
In SQL data integrity is the overall accuracy, completeness and consistency of the data. It also refers to the safety of data in terms of compliance. Data integrity also ensures that your data is safe from any outside forces.
In SQL query is one type of questions we are asking database table. Generally when we ask question, we expect some result in return, so same way in database when we query database it will return some data as per condition we have passed to query. We can query to single table or multiple tables and also filter records based of perticular conditions. It can be simple to very complex.
A sub query is the query within main query. It is also called nested query which will provide data to the parent query. Generally it will be enclosed between parenthesis. It can return single value or multiple rows as well. We can join with subquery too.
The SELECT statement is used to retrieve data from the data table which returns data in the tabular format, is is called result set.
The SELECT statement has some common clauses as below.
The UNION operator is used to combine two result set of two different select query, it combines two result set into one. In other word we can see the result set obtained using union is collection of the both result set as far as columns are same, order of columns is same and data type of column should be same or compatible with each other.
The MINUS operator is used to retrieved only distinct rows from the first resultset. It must to follow the conditions that we have seen in the union clause like columns must be same, order of the column must be same and datatype should be same or should be compatible with each other.
The INTERSECT operator is used to filter common records from two result set of two different select query, it checks distincts records from both result set. It must to follow the conditions that we have seen in the union clause like columns must be same, order of the column must be same and datatype should be same or should be compatible with each other.
SQL Server curosr process a result set one row at a time, it processed all the row one by one. SQL works on result set, select statement return bunch of rows from the database and sometimes you want to process one by one row from result set then cursor will come into the picture. Cursor can be used to process all the row one by one.
When ever we are storing one entiti's records into two table then we can say those data has relationship between them. There are different types of relationships as below in SQL Server.
In SQL server alias is very important feature, we can give alias for columns and tables as well, here we will see both example how to use alias for columns and table. In SQL Server when we write select query and select columns then in the resultset it will display column name as heading see below example,
SELECT firstname, lastname FROM Employee;
It will return result set as below
In the above result we can see the column headings are used as firstname and lastname respectivly. Now lets say if we want to display employee full name as one column so we need to concat both column like below
SELECT firstname + ' ' + lastname FROM Employee;
It will return result set as below
|(No column name)|
In the above result we can see column heading has not given any name to the column so here we can use column alias like below so it will give some meaningful name to the column. First we will see the syntax for column alias as below
column_heading | expression AS column_heading_alias
In the above syntax keyword AS is an optional. Following is an example to use an column alias.
SELECT firstname + ' ' + lastname AS 'Employee Name' FROM Employee;
If alias name contains space then we need to enclose it into quatation mark as shown in the above example.
Table alias is also same like column alias, in joining cluase we are using table alias to get the column name from perticular table then we can give table alias like below
SELECT e.firstname, e.lastname, d.name, e.dateofbirth FROM employee e INNER JOIN department d ON d.id = e.departmentid;
In the above example e and d both are table alias so we can make the table name shorter while accessing column name form perticular table.
Views in SQL are similar to virtual tables, it has rows and columns from 1 for more tables. It can have all the columns or selected columns from the table and rows are based on the certain condition.
Normalization is a process of structuring the data in database in a way which can avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly.
Denormalization is a technique to optimize the database in which we add redundant data to one or more than one tables. Denormalization can help us avoid costly joins in a relational database. Here please note that denormalization does not mean that we are not doing normalization. It is an optimization technique that is applied after doing normalization.
In RDBMS below are the forms of Normalization
In SQL there are a multiple of ways to delete data, including the DELETE, TRUNCATE TABLE and DROP TABLE commands. Now we need to understand which one should we use in a given situation? So lets see in which situation which statement we need to use, use DELETE : if we want to remove some specific rows from a given table. use TRUNCATE : if we want to remove all the data from table and need the blank table structure, It is faster then DELETE. use DROP TABLE: if we want to remove entire table along with it's data and structure.
In SQL it's providing some functions which are being used to do some operations on the data, those are built-in functions, they are categorised in two different category and both category has 7 functions as below
These functions are used to do some operations on the column value of the table and it's return single value only
These functions are based on user input, these too returns single value.
In SQL user defined function (UDF) is a programming construct which accepts some parameters, does some work using accepted params and returns a type of result. There are two types of UDF in SQL table=-valued and scalar-valued.