Saturday, 5 March 2016

SQL Based IT study material for IBPS/LIC AAO EXAM

SQL Queries IT Study Material for IBPS lic aao exam: Data Base Management System is one of the important concept for candidates who are preparing for IBPS Specialist Officer Exam. Questions from these concepts will be asked in Professional Knowledge section. To help the candidates here we are updating materials on various important concepts of DBMS. We already uploaded Data Models and RDBMS Material and now we are updating SQL Material for candidates. After checking out all the previous papers and grabbing knowledge we can say candidates can gain around 10 marks by preparing with all the major concepts of DBMS.

Structure Query Language (SQL) is a non-procedural language used for storing and managing data in RDBMS. It is also known as 4GL. SQL defines following data languages to manipulate data of RDBMS. 

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE - to create table in the database
ALTER - alters the structure of the database
DROP – deletes entire table from the database
TRUNCATE - remove all records from a table
RENAME - rename an object

Data Manipulation Language (DML) statements are used for managing data in database. DML commands are not auto-committed, it can be rolled back.

Insert command is used to insert data into a table.
Update command is used to update a row of a table
Delete command is used to delete data from a table

Data Control Language (DCL) is used to control privilege in Database. DCL defines two commands.

Grant: Gives user access privileges to database.
Revoke: Take back permissions from user

Transaction Control Language (TCL) commands are used to manage transactions in database.

Commit: used to permanently save any transaction into database.
Rollback: This command restores the database to last committed state.
Savepoint : used to temporarily save a transaction so that you can rollback to that point whenever necessary.

Every single mark in the exam is important for candidates because each and every mark decides the candidate’s future. Now here we are uploading SQL Materials for the candidates as it is the most important topic to cover. As per our knowledge we can say candidates can easily gain one to two marks from this concept.
Hopefuls preparing for the IBPS Specialist Officer recruitment process is  advised to stay tuned to us as well will provide regular updates for the candidates about the exam.

Overview of SQL Queries:

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database.
SQL is the standard language for Relation Database System. All relational  database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language.
Originally based upon relational algebra and tuple relational calculus, SQL consists of a data definition language and a data manipulation language.

SQL Commands:
The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature as:

DDL – Data Definition Language:
Create              :           creates a new table, a view of a table, or other object in database.
Alter                :           Modifies an existing database object, such as a table.
Drop                :           Deletes an entire table, a view of a table or other object in the database.
DML – Data Manipulation Language:
Select              :           Retrieves certain records from one or more tables
Insert               :           Creates a record
Update                        :           Modifies records
Delete              :           Deletes records

DCL – Data Control Language:
Grant               :           Gives a privilege to user
Revoke            :           Takes back privileges granted from user

Important Terms:

Primary Key: The primary key in a relational table uniquely identifies each record in the table, it may consists of a single attribute or multiple attributes in combination
Primary key must contain unique values
A primary key column cannot contain NULL values.
Each table can have only one primary key
If multiple records or fields are used as primary key then it is called “Composite Key”
Let us consider a table called “STUDENT” which contains records namely first name, last name, branch, and Student ID. Here “Student ID” will be the primary key as there are chances that more than one student may have same same branch or name

Foreign Key:
Foreign key is a field or collection of fields in one table that uniquely identifies a row of another table
Foreign key sometimes called as referencing key, it is used to link two tables together’
Foreign key in one table points to a Primary Key in another table
Example: Let us consider two tables Student and Student Details, Student table contains primary key i.e. Student_ID and student details has foreign key which references to primary key of first table in order to identify the unique relationship

Unique Key: Uniquely identifies each record in a database table
More than one unique key is allowed in one table
It can be Null and may not be unique
It can’t be candidate key

SQL Queries
SELECT Column1, 2…… ColumnN FROM table_name;
SELECT DISTINCT Column1, 2…… ColumnN FROM table_name;
WHERE Clause:
SELECT  Column1, 2…… ColumnN FROM table_name WHERE CONDITION;
AND/OR Clause:
SELECT  Column1, 2…… ColumnN FROM table_name WHERE CONDITION-1 {AND | OR} CONDITION-2;
IN Clause
SELECT  Column1, 2…… ColumnN FROM table_name WHERE column-name IN (val-1, val-2, …. Val-N);
SELECT column1, column2…….columnN FROM column-name BETWEEN val-1 AND val-2;
LIKE Clause
SELECT column1, column2…….columnN FROM table-name WHERE column-name LIKE { PATTERN } ;
ORDER BY Clause:
SELECT column1, column2…….columnN FROM table-name WHERE CONDITION ORDER BY column-name {ASC | DESC};
GROUP BY Clause:
SELECT SUM (column-name) FROM table-name WHERE CONDITION GROUP BY column-name;
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
columnN datatype,
PRIMARY KEY ( one or more columns)
DROP TABLE table_name;
CREATE UNIQUE INDEX index-name ON table-name ( column1, column2,……column);
DROP INDEX Statement:
ALTER TABLE table-name DROP INDEX index-name;
DESC Statement
 DESC table_name;
TRUNCATE TABLE table_name;
ALTER TABLE Statement:
ALTER TABLE table_name {ADD|DROP|MODIFY} column-name {data_type}
UPDATE Statement:
UPDATE table_name SET column1=value1, column2 = value 2…….. columnN = value N { WHERE CONDITION ];
DELETE Statement:

No comments:

Post a Comment