What is SQL?
SQL stands for Structured Query Language. A query language is a kind of programming language that’s designed to retrieving and stored specific information from databases, and that’s exactly what SQL does SQL is the language of databases. where database management system handle this SQL.
Why SQL is required?
SQL is required because it offers the following advantages for the users.
∙ SQL helps in creating new databases, views, and tables.
∙ It is used for inserting, updating, deleting and retrieving data records in the database ∙ It enables users to interact with data stored in relational database management systems. ∙ SQL is required to create a view, stored procedure, functions in a database
Commands that are used in SQL:
DDL commands: Data Definition Language Commands
1 Create: it is used to create a database.
Syntax:create database <database name>;
Example: create database welcome;
2 Alter: if we want add column or drop column in the existing database then we use alter command for it.
Syntax:for add column
Alter table <table-name> add <column name> <datatype>;
For drop column
Alter table <table-name> drop <column name>;
3 Truncate:this truncate command is used to delete all rows or records or tuples into the table at a time. In this truncate command where clause is not use and when we will used truncate command the rollback command will not be used to recover that data.
Syntax:Truncate table <table-name>;
DML commands: DML stands for Data Manipulation Language commands. 1 Insert:whenever we want to insert a record into the table then we use insert command Syntax:insert into <table-name> values(values are provide here);
2 Delete:whenever we want to delete tuples from table then we use delete command. 1 delete one row:we use where clause in this delete command.
Syntax:delete from <table-name > where <column-name>;
2 delete all rows:
Syntax:delete from <table-name>;
3 Update:whenever any modification performed on to existing table we use update command.
Syntax:update <table-name> set <column-name> where <column-name>; DQL commands:Dql stands for Data Query Language commands: 1 Select:whenever in this database we created table and after we inserted values in to table Then we fetched all records using select command.
Syntax:select * from <table-name>;
Whenever we want to fetched only one column then we wil use
Syntax:select <column name> from <table-name>;
TCL commands:Tcl Stands for Transactional control Language commands:
1 Commit:By default your sql server is auto-commit,whenever used to save the changes made in to table permanent.
2 Rollback:used to set back previous permanent status of the table it is similar kind of undo. Syntax:Rollback;
Table can be rolled-back if it is temporary if you commited changes,it can not be rollbacked. 3 SavePoint:used to along rollback mark a transaction in table.
DCL commands:Dcl stands for Data control Language.
DCl is used to control priviledges in database to perform any operation in database such as creating table,deleting table,insert record into the table.
1 Grant:used to provide user access priviledges or other priviledges for database. 2 Revoke:Take back the permission from the user.
Question and answer of Sql in Interview.
1 What is difference between delete and truncate command?
Ans:Delete is Dml command which is used to delete one row or all rows into the table and we used rollback command after the delete command where as truncate is Ddl command used to only delete all rows from the table we cant use where clause in truncate command and not used rollback command after truncate that data will not be recovered.
2 What is Database Management System?
Ans:DBMS is collecting the inter-related Data from user to stored and retrieve it with the security measures.
3 What are different Types of Data:?
There are 3 different types of Data:
1 structured Data
3 Semi-Structured Data
4 What is command used for Pattern Matching?
Ans:Like operator is used to Pattern Matching
There are 2 wildcards used for pattern matching.
1 %(percentage):used to single or multiple character.
2 _(underscore):used to only one or single character.
5 How many aggregate function?
Ans:In Sql there are different types of Aggregate function.
1 count:it is used to provide how many records are present into table
2 sum:to calculate summation of salary or any interger attribute into the table 3 min:to find minimum value
4 max: to find the Maximum value
5 Avg:to find average of salary of any interger column in to table.
Sql Operators:An operator which are reserved word or character that are used in sql. In which these operator used within where clause to perform a specific operation such as comparison,arithmetic operation.
There are different types of operator
1 Arithmetic Operator:In this operator we used some variable and doing arithmetic operation on it.
Operators are like addition(+),subtraction(-),multiplication(*),division(/),Modulus(%) these are called arithmetic operators.
2 Comparison Operator:Equal to(=) ,not Equal to(!=),less than(<),greater than(>),greater than or equal to(>=),less than or equal to (<=) these are called comparison operators.
3 Logical operators:
1 All:all operator is used to compare all values in the given set.
2 And:the And operator allows the existence of multiple condition in an sql statememts where clause.
3 Any:The any operator is used to compare a value to any applicable value in the list according to condition.
4 Between:the values are present in this operator is the inclusive that’s are included in the output.
5 In: The in operator is short hand of Or operator.i.e to compare all the value with similar value. 6 Like:it is used for pattern matching.
7Or:the or operator is used to combine multiple condition in Sql statement where clause. 8 Is Null:the null operator that are used to compare the Null values.
9 Unique:the Unique operator searches every row of specified table for uniqueness and also no duplicate are allowed over here.
Constaints In sql:
Constraints are the rules applied at the time of create or alter table.and also it is called the set of protocols.this ensures the accuracy and reliability of the data in to database.
Constraints are applied on column level or table level.column level constraints are applied to only one column and table level are applied to the whole table.
There are different types of Constraints:
1 Not Null:Ensures that column cannot have a Null value all the Non Null values that are present into table.
2 Unique:All values are present in the column that are different and also unique values.we don’t want duplicate values.
3 Primary Key:All the values or records are indentifies each row uniquely. In this primary key contain Not Null values and unique.
4 Foreign Key:Uniquely indentified a rows or record in any other database table. 5 Check:The check constraint ensures that all values in a column satisfy certain condition. 6 Default:Provides a default value for a column when none is specified. What is RDBMS?
RDBMS is stands for relational database management System.which is the basic sql and stored the all the data in the form of the table.In the Rdbms there are different types of Concepts such as field,table,Record or Row,column etc.
What is Null Value?
A null value in table it is means There is no appropriate value or meaningless value.and most important Null values are different than Zero value.
SQL Magic Words:
In this sql there are three magic Words are present that is select,from and where .select is used for retrieving data and where clause is used to filtered out output with filtered out condition and from is used for where that table which is to be record is fetched.
What is Normalization:
Process of the minimizes redundancy and we removes total 3 anomalies insertion ,updation and deletion also Normalization divides the larger table into smaller table and links them using relationship using primary key and foreign key.
Author: Joshi, Vaibhav
SevenMentor Pvt Ltd
Call the Trainer and Book your free demo Class for JAVA now!!!
© Copyright 2021 | Sevenmentor Pvt Ltd.