- Information
- AI Chat
Sql - sql practice
sql practice
Course
Data Analytics - Trainity
28 Documents
Students shared 28 documents in this course
University
Visvesvaraya Technological University
Academic year: 2022/2023
Uploaded by:
Anonymous Student
This document has been uploaded by a student, just like you, who decided to remain anonymous.
Visvesvaraya Technological UniversityRecommended for you
Preview text
SQL
[Structured Query Language]
PLACEMENT PREPARATION [EXCLUSIVE NOTES]
MUST SAVE AND SHARE
Curated By- HIMANSHU KUMAR(LINKEDIN)
####### linkedin/in/himanshukumarmahuri
QUESTIONS COVERED:-
1. What is SQL?
2. What is Database?
3. What are the differences between
SQL and PL/SQL?
4. What is the difference between
BETWEEN and IN operators in
SQL?
5. What is the use of LIKE operator?
6. What is the use of ‘WHERE’
Clause?
7. What is the use of ‘Having’
Clause?
8. What are SQL Commands?
Explain types of SQL Commands
9. What are normalization and
denormalization and why do we
need them?
10. What are Nested Queries in SQL?
11. What are different types of
Normalization?
12. What is Stored Procedures in SQL
?
13. What are different types of case
manipulation functions available in
SQL.
14. What is the difference between
CHAR and VARCHAR2 datatype
in SQL?
15. What is the use of CREATE,
INSERT INTO, UPDATE and
DELETE Clauses?
16. What is the use of ADD, DROP
and MODIFY Commands?
17. What are VIEWS in SQL?
18. What are JOINS in SQL?
19. What is the use of GROUP BY
Clause?
20. What are Aggregate Functions?
21. What is Cursor in SQL?
22. What is the difference between
Implicit and Explicit Cursor?
23. What is the difference between
VIEW and CURSOR in SQL?
24. What are the advantages of
PL/SQL functions?
25. Explain BETWEEN and IN
Clause.
26. What is the difference between
DROP and TRUNCATE?
27. What are Constraints in SQL?
28. What is a TRIGGER?
29. What is the use of LIMIT and
OFFSET in SQL?
30. What are different types of
operators present in SQL?
What is SQL?
Structured Query Language is a computer language that we use to interact with a
relational database is a tool for organizing, managing, and retrieving archived
data from a computer database. The original name was given by IBM as Structured
English Query Language, abbreviated by the acronym SEQUEL. When data needs
to be retrieved from a database, SQL is used to make the request. The DBMS
processes the SQL query retrieves the requested data and returns it to us. Rather,
SQL statements describe how a collection of data should be organized or what data
should be extracted or added to the database.
In common usage, SQL encompasses DDL and DML commands for create, updates,
modified or other operations on database structure.
SQL uses:
- Data definition: It is used to define the structure and organization of the
stored data and relationships among the stored data items.
- Data retrieval: SQL can also be used for data retrieval.
- Data manipulation: If the user wants to add new data, remove data, or modifying in
existing data then SQL provides this facility also.
- Access control: SQL can be used to restrict a user’s ability to retrieve, add, and
modify data, protecting stored data against unauthorized access.
- Data sharing: SQL is used to coordinate data sharing by concurrent users, ensuring
that changes made by one user do not inadvertently wipe out changes made at
nearly the same time by another user.
SQL also differs from other computer languages because it describes what the
user wants the computer to do rather than how the computer should do it. (In more
technical terms, SQL is a declarative or descriptive language rather than a
procedural one.) SQL contains no IF statement for testing conditions, and no GOTO,
DO, or FOR statements for program flow control. Rather, SQL statements describe
how a collection of data is to be organized, or what data is to be retrieved or added
to the database. The sequence of steps to do those tasks is left for the DBMS to
determine.
Features of SQL:
- SQL may be utilized by quite a number of users, which include people with very
little programming experience.
SQL is a Non-procedural language.
SQL is a distributed database language. Distributed database control structures use
SQL to assist distribute facts throughout many linked pc structures. The
DBMS software program on every gadget makes use of SQL to speak with the
opposite structures, sending requests for facts to get entry to.
- SQL is a database gateway language. In a pc community with a mixture of
various DBMS products, SQL is frequently utilized in a gateway that lets
in one logo of DBMS to speak with every other logo. SQL has for this
reason emerged as a useful, effective device for linking people, pc packages,
and pc structures to the facts saved in a relational database.
Finally, SQL is not a particularly structured language, especially when compared
with highly structured languages such as C, Pascal, or Java. Instead, SQL
statements resemble English sentences, complete with “noise words” that don’t
add to the meaning of the statement but make it read more naturally. The SQL has
quite a few inconsistencies and also some special rules to prevent you from
constructing SQL statements that look perfectly legal but that don’t make sense.
Despite the inaccuracy of its name, SQL has emerged as the standard language for
using relational databases. SQL is both a powerful language and one that is
relatively easy to learn. So SQL is a database management language. The database
administrator answerable for handling a minicomputer or mainframe database
makes use of SQL to outline the database shape and manipulate get entry to to the
saved data.
What is Database?
The Database is an essential part of our life. As we encounter several activities that
involve our interaction with databases, for example in the bank, in the railway
station, in school, in a grocery store, etc. These are the instances where we need to
store a large amount of data in one place and fetch these data easily.
A database is a collection of data that is organized, which is also called structured
data. It can be accessed or stored in a computer system. It can be managed through
a Database Management System (DBMS), a software used to manage data.
Database refers to related data in a structured form.
In a database, data is organized into tables consisting of rows and columns and it is
indexed so data can be updated, expanded, and deleted easily. Computer
databases typically contain file records data like transactions money in one bank
account to another bank account, sales and customer details, fee details of
students, and product details. There are different kinds of databases, ranging from
the most prevalent approach, the relational database, to a distributed database,
cloud database, and NoSQL databases.
- Relational Database:
A relational database is made up of a set of tables with data that fits into a
predefined category.
- Distributed Database:
A distributed database is a database in which portions of the database are
stored in multiple physical locations, and in which processing is dispersed or
replicated among different points in a network.
- Cloud Database:
A cloud database is a database that typically runs on a cloud computing
platform. Database service provides access to the database. Database
services make the underlying software-stack transparent to the user.
These interactions are the example of a traditional database where data is of one
type-that is textual. In advancement of technology has led to new applications of
database systems. New media technology has made it possible to store images,
video clips. These essential features are making multimedia databases.
Nowadays, people are becoming smart - before taking any decisions they analyze
facts and figures related to it, which come from these databases. As the databases
have made it easier to manage information, we are able to catch criminals and do
deep research.
What is the use of LIKE operator?
The LIKE operator of SQL is used for this purpose. It is used to fetch filtered data by
searching for a particular pattern in the where clause.
The Syntax for using LIKE is,
SELECT column1,column2 FROM table_name WHERE column_name LIKE pattern;
LIKE: operator name
pattern: exact value extracted from the pattern to get related data in
result set.
For Example: Find all employees from the table 'Employees' whose name start with an 'A'.
The required query is:
SELECT * FROM Employees WHERE EmpName like 'A%' ;
What is the use of ‘WHERE’ Clause?
WHERE keyword is used for fetching filtered data in a result set.
- It is used to fetch data according to a particular criteria.
- WHERE keyword can also be used to filter data by matching patterns.
Basic Syntax:
SELECT column1,column2 FROM table_name WHERE column_name operator value;
column1 , column2: fields int the table
table_name: name of table
column_name: name of field used for filtering the data
operator: operation to be considered for filtering
value: exact value or pattern to get related data in result
List of operators that can be used with where clause:
EXAMPLE -
These SQL commands are mainly categorized into four categories as:
1. DDL - Data Definition Language
2. DQl - Data Query Language
3. DML - Data Manipulation Language
4. DCL - Data Control Language
Though many resources claim there to be another category of SQL clauses TCL -
Transaction Control Language. So we will see in detail about TCL as well.
1. DDL(Data Definition Language): DDL or Data Definition Language actually
consists of the SQL commands that can be used to define the database schema. It
simply deals with descriptions of the database schema and is used to create and
modify the structure of database objects in the database.
Examples of DDL commands:
o CREATE - is used to create the database or its objects (like table, index,
function, views, store procedure, and triggers).
o DROP - is used to delete objects from the database.
o ALTER -is used to alter the structure of the database.
o TRUNCATE–is used to remove all records from a table, including all spaces
allocated for the records are removed.
o COMMENT – is used to add comments to the data dictionary.
o RENAME – is used to rename an object existing in the database.
2. DQL (Data Query Language) :
DML statements are used for performing queries on the data within schema objects.
The purpose of the DQL Command is to get some schema relation based on the
query passed to it.
Example of DQL:
o SELECT – is used to retrieve data from the database.
3. DML(Data Manipulation Language): The SQL commands that deal with the
manipulation of data present in the database belong to DML or Data Manipulation
Language and this includes most of the SQL statements.
Examples of DML:
o INSERT – is used to insert data into a table.
o UPDATE - is used to update existing data within a table.
o DELETE – is used to delete records from a database table.
4. DCL(Data Control Language) : DCL includes commands such as GRANT and
REVOKE which mainly deal with the rights, permissions, and other controls of the
database system.
Examples of DCL commands:
o GRANT - gives user’s access privileges to the database.
o REVOKE-withdraw user's access privileges given by using the GRANT
command.
5. TCL(transaction Control Language): TCL commands deal with the transaction
within the database.
Examples of TCL commands:
o COMMIT– commits a Transaction.
o ROLLBACK– rollbacks a transaction in case of any error occurs.
o SAVEPOINT – sets a savepoint within a transaction.
o SET TRANSACTION – specify characteristics for the transaction.
STUDENT_COURSE tables for understanding nested queries.
STUDENT
S_ID S_NAME S_ADDRESS S_PHONE S_AGE
S1 RAM DELHI 9455123451 18
S2 RAMESH GURGAON 9652431543 18
S3 SUJIT ROHTAK 9156253131 20
S4 SURESH DELHI 9156768971 18
COURSE
C_ID C_NAME
C1 DSA
C2 Programming
C3 DBMS
STUDENT_COURSE
S_ID C_ID
S1 C
S1 C
S2 C
S3 C
S4 C
S4 C
There are mainly two types of nested queries:
- Independent Nested Queries: In independent nested queries, query
execution starts from innermost query to outermost queries. The execution of
the inner query is independent of the outer query, but the result of the inner
query is used in the execution of the outer query. Various operators like IN,
NOT IN, ANY, ALL etc are used in writing independent nested queries.
IN: If we want to find out S_ID who are enrolled in C_NAME ‘DSA’ or
‘DBMS’, we can write it with the help of independent nested query and IN
operator. From COURSE table, we can find out C_ID for C_NAME ‘DSA’ or
DBMS’ and we can use these C_IDs for finding S_IDs
from STUDENT_COURSE TABLE.
STEP 1: Finding C_ID for C_NAME =’DSA’ or ‘DBMS’
Select C_ID from COURSE where C_NAME = ‘DSA’ or C_NAME = ‘DBMS’
STEP 2: Using C_ID of step 1 for finding S_ID
Select S_ID from STUDENT_COURSE where C_ID IN
(SELECT C_ID from COURSE where C_NAME = ‘DSA’ or C_NAME=’DBMS’);
The inner query will return a set with members C1 and C3 and the outer query will
return those S_IDs for which C_ID is equal to any member of the set (C1 and C3 in
this case). So, it will return S1, S2 and S4.
Note: If we want to find out names of STUDENTs who have either enrolled in ‘DSA’
or ‘DBMS’, it can be done as:
Select S_NAME from STUDENT where S_ID IN
(Select S_ID from STUDENT_COURSE where C_ID IN
(SELECT C_ID from COURSE where C_NAME=’DSA’ or C_NAME=’DBMS’));
NOT IN: If we want to find out S_IDs of STUDENTs who have neither enrolled in
‘DSA’ nor in ‘DBMS’, it can be done as:
Select S_ID from STUDENT where S_ID NOT IN
(Select S_ID from STUDENT_COURSE where C_ID IN
(SELECT C_ID from COURSE where C_NAME=’DSA’ or C_NAME=’DBMS’));
The innermost query will return a set with members C1 and C3. Second inner query
will return those S_IDs for which C_ID is equal to any member of set (C1 and C3 in
this case) which are S1, S2 and S4. The outermost query will return those S_IDs
where S_ID is not a member of set (S1, S2 and S4). So it will return S3.
3NF. It is also referred to as 3 for the same reason. A table to be in its BCNF
normal form should satisfy the following conditions:
○ The table should be in its 3NF i. satisfy all the conditions of 3NF.
○ For every functional dependency of any attribute A on B
(A->B), A should be the super key of the table. It simply implies that A can’t
be a non-prime attribute if B is a prime attribute
What is Stored Procedures in SQL?
Stored Procedures are created to perform one or more DML operations on
Database. It is nothing but the group of SQL statements that accepts some input in
the form of parameters and performs some task and may or may not returns a
value.
Syntax : Creating a Procedure
CREATE or REPLACE PROCEDURE name(parameters)
IS
variables;
BEGIN
//statements;
END;
The most important part is parameters. Parameters are used to pass values to the
Procedure. There are 3 different types of parameters, they are as follows:
1. IN:
This is the Default Parameter for the procedure. It always receives the values
from calling program.
2. OUT:
This parameter always sends the values to the calling program.
3. IN OUT:
This parameter performs both the operations. It Receives value from as well
as sends the values to the calling program.
Example:
Imagine a table named with emp_table stored in Database. We are Writing a
Procedure to update a Salary of Employee with 1000.
CREATE or REPLACE PROCEDURE INC_SAL(eno IN NUMBER, up_sal OUT NUMBER)
IS
BEGIN
UPDATE emp_table SET salary = salary+1000 WHERE emp_no = eno;
COMMIT;
SELECT sal INTO up_sal FROM emp_table WHERE emp_no = eno;
END;
- Declare a Variable to Store the value coming out from Procedure :
VARIABLE v NUMBER;
- Execution of the Procedure:
EXECUTE INC_SAL(1002, :v);
- To check the updated salary use SELECT statement:
SELECT * FROM emp_table WHERE emp_no = 1002;
- or Use print statement :
A Database is defined as a structured set of data. So, in SQL the very first
step to store the data in a well structured manner is to create a database.
The CREATE DATABASE statement is used to create a new database in
SQL.
Syntax:
CREATE DATABASE database_name;
database_name: name of the database.
Example Query: This query will create a new database in SQL and name the
database as university.
CREATE DATABASE university;
CREATE TABLE
We have learned above about creating databases. Now to store the data we
need a table to do that. The CREATE TABLE statement is used to create a
table in SQL. We know that a table comprises rows and columns. So while
creating tables we have to provide all the information to SQL about the
names of the columns, type of data to be stored in columns, size of the data,
etc. Let us now dive into details on how to use the CREATE TABLE
statement to create tables in SQL.
Syntax:
CREATE TABLE table_name
(
column1 data_type(size),
column2 data_type(size),
column3 data_type(size),
....
);
table_name: name of the table.
column1 name of the first column.
data_type: Type of data we want to store in the particular
column.
For example,int for integer data.
size: Size of the data we can store in a particular column. For
example, if for
a column we specify the data_type as int and size as 10 then this
column can store an integer
a number of maximum 10 digits.
Example Query: This query will create a table named Students with four
columns, ROLL_NO, NAME, ADDRESS, and AGE.
CREATE TABLE Student
(
ROLL_NO int,
AGE int,
NAME varchar(20),
ADDRESS varchar(20)
);
This query will create a table named Student. The ROLL_NO and AGE field is
of type int. The next two columns NAME and ADDRESS are of type varchar
and can store characters and the size 20 specifies that these two fields can
hold a maximum of 20 characters.
2. INSERT INTO Clause
The INSERT INTO statement of SQL is used to insert a new row in a table.
There are two ways of using INSERT INTO statement for inserting rows:
0. Only values: First method is to specify only the value of data to be
inserted without the column names.
Syntax
INSERT INTO table_name VALUES (value1, value2, value3,...);
table_name: name of the table.
value1, value2,.. : value of first column, second
column,... for the new record
1. Values with Column Name: In the second method we will specify
both the columns which we want to fill and their corresponding values
as shown below:
Syntax:
INSERT INTO table_name (column1, column2, column3,..)
VALUES ( value1, value2, value3,..);
table_name: name of the table.
column1: name of first column, second column ...
value1, value2, value3 : value of first column, second
column,... for the new record
Empty Student table After Creation
Was this document helpful?
Sql - sql practice
Course: Data Analytics - Trainity
28 Documents
Students shared 28 documents in this course
University: Visvesvaraya Technological University
Was this document helpful?
pg. 1 | HIMANSHU KUMAR(LINKEDIN)
SQL
[Structured Query Language]
PLACEMENT PREPARATION [EXCLUSIVE NOTES]
MUST SAVE AND SHARE
Curated By- HIMANSHU KUMAR(LINKEDIN)
https://www.linkedin.com/in/himanshukumarmahuri
QUESTIONS COVERED:-
1. What is SQL?
2. What is Database ?
3. What are the differences between
SQL and PL/SQL?
4. What is the difference between
BETWEEN and IN operators in
SQL?
5. What is the use of LIKE operator?
6. What is the use of ‘WHERE’
Clause?
7. What is the use of ‘Having’
Clause?
8. What are SQL Commands?
Explain types of SQL Commands
9. What are normalization and
denormalization and why do we
need them?
10. What are Nested Queries in SQL?
11. What are different types of
Normalization?
12. What is Stored Procedures in SQL
?
13. What are different types of case
manipulation functions available in
SQL.
14. What is the difference between
CHAR and VARCHAR2 datatype
in SQL?
15. What is the use of CREATE,
INSERT INTO, UPDATE and
DELETE Clauses?
16. What is the use of ADD, DROP
and MODIFY Commands?
17. What are VIEWS in SQL?
18. What are JOINS in SQL?
19. What is the use of GROUP BY
Clause?
20. What are Aggregate Functions?
21. What is Cursor in SQL ?
22. What is the difference between
Implicit and Explicit Cursor?
23. What is the difference between
VIEW and CURSOR in SQL?
24. What are the advantages of
PL/SQL functions?
25. Explain BETWEEN and IN
Clause.
26. What is the difference between
DROP and TRUNCATE?
27. What are Constraints in SQL?
28. What is a TRIGGER?
29. What is the use of LIMIT and
OFFSET in SQL?
30. What are different types of
operators present in SQL?
Discover more from:
- Discover more from:
