SQL(Structure Query Language)

SQL(Structure Query Language) 

Topics :

A. (DML) Data Manipulation Language - Non Query
B. (DDL) Data Definition Language - Execute Query
C. Database Clauses
D. Aggregate Functions (SQL Functions)
E. Database Alas
F. Database Constraints
G. Categories of Keys  - Database management
H. Joining in Database
I. Database View -imp
J. Store Procedure -imp
K. (DCL) Data Control Language

A. (DML) Data Manipulation Language - Non Query

    1. SELECT
    2. INSERT
    3. UPDATE
    4. DELETE


B. (DDL) Data Definition Language - Execute Query

    1. CREATE
    2. ALTER

-  The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
-  The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Add Column
ALTER TABLE Customers
ADD Email varchar(255);

Drop Column
ALTER TABLE Customers
DROP COLUMN Email;

Rename Column
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

Modify Datatype of  Column
ALTER TABLE table_name
ALTER COLUMN column_name datatype;

    3. DROP
    4. TRUNCATE
    5. RENAME


C. Database Clauses

    1. WHERE

- WHERE keyword is used for fetching filtered data in a result set. It is used to fetch data according to particular criteria. WHERE keyword can also be used to filter data by matching patterns.

SELECT column1,column2 FROM table_name WHERE column_name operator value;

    2. BETWEEN

- The SQL BETWEEN condition allows you to easily test if an expression is within a range of values (inclusive). The values can be text, date, or numbers. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement. The SQL BETWEEN Condition will return the records where the expression is within the range of value1 and value2.

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

Using the NOT Operator with BETWEEN
Find all the Employee names whose salary is not in the range of 30000 and 45000.
SELECT Name
FROM Emp 
WHERE Salary
NOT BETWEEN 30000 AND 45000;
    3. GROUPBY

- The SQL GROUP BY clause groups rows of data with the same values in the specified columns. This clause is most commonly used with SQL aggregate functions to compute statistics (such as a count of certain values, sum, average, and the minimum/maximum value in a set) for a group of rows. Here’s a simple example:

SELECT
  country,
  COUNT(*)
FROM box_office
GROUP BY country;

    4. ORDERBY

- ORDER BY. This command sorts the query output in ascending (1 to 10, A to Z) or descending (10 to 1, Z to A) order. The ascending sort is the default; if you omit the ASCending or DESCending keyword, the query will be sorted in ascending order. You can specify the sort order using ASC or DESC. Here’s a simple example:

SELECT
  movie,
  city,
  gross
FROM box_office
ORDER BY gross;

SELECT movie
FROM box_office
GROUP BY movie
ORDER BY movie ASC;

    5. IN

- IN operator allows you to easily test if the expression matches any value in the list of values. It is used to remove the need for multiple OR conditions in SELECT, INSERT, UPDATE, or DELETE. You can also use NOT IN to exclude the rows in your list. We should note that any kind of duplicate entry will be retained.
 
Find the Fname, and Lname of the Employees who have a Salary equal to 30000, 40000, or 25000.
SELECT Name
FROM Emp
WHERE Salary IN (30000, 40000, 25000);

    6. EXIST

- The EXISTS operator is used to test for the existence of any record in a subquery.
- The EXISTS operator returns TRUE if the subquery returns one or more records.

The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

     
    6. Having

- The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

D. Aggregate Functions (SQL Functions)

    1. SUM
    2. AVG
    3. COUNT
    4. MIN
    5. MAX
    6. TOP
    7. CAST

E. Database Alias

Aliases are the temporary names given to tables or columns for the purpose of a particular SQL query. It is used when the name of a column or table is used other than its original name, but the modified name is only temporary.

  • Aliases are created to make table or column names more readable.
  • The renaming is just a temporary change and the table name does not change in the original database.
  • Aliases are useful when table or column names are big or not very readable.
  • These are preferred when there is more than one table involved in a query.
SELECT CustomerID AS SSN FROM Customer;

F. Database Constraints

- Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);


- SQL constraints are used to specify rules for the data in a table.

- Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

- Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

  • NOT NULL - Ensures that a column cannot have a NULL value
  • UNIQUE - Ensures that all values in a column are different
  • PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
            CREATE TABLE Persons (
                ID int NOT NULL,
                LastName varchar(255) NOT NULL,
                FirstName varchar(255),
                Age int,
                PRIMARY KEY (ID)
            );
  • FOREIGN KEY - Prevents actions that would destroy links between tables
            CREATE TABLE Orders (
                OrderID int NOT NULL,
                OrderNumber int NOT NULL,
                PersonID int,
                PRIMARY KEY (OrderID),
                FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
            );
  • CHECK - Ensures that the values in a column satisfies a specific condition
            CREATE TABLE Persons (
                ID int NOT NULL,
                LastName varchar(255) NOT NULL,
                FirstName varchar(255),
                Age int,
                CHECK (Age>=18)
            );
  • DEFAULT - Sets a default value for a column if no value is specified
            CREATE TABLE Persons (
                ID int NOT NULL,
                LastName varchar(255) NOT NULL,
                FirstName varchar(255),
                Age int,
                City varchar(255) DEFAULT 'Sandnes'
            );
  • CREATE INDEX - Used to create and retrieve data from the database very quickly


G. Categories of Keys  - Database management

    1. Primary Key
    2. Unique Key
    3. Foreign Key


H. Joining in Database

    1. Inner join

The INNER JOIN keyword selects records that have matching values in both tables.

SELECT ProductID, ProductName, CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;





    2. Left outer join / Left Join

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;




    3. Right outer join / Right Join
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;




    3. Full outer join / Full Join

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

Tip: FULL OUTER JOIN and FULL JOIN are the same.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;



    5. Cross Join

In SQL, a Cross Join is also called a Cartesian Join, it performs cross product of records of two or more joined tables. Sometimes we need to match each row of one table to every other row of another table so in this case cross Join is the best choice. Performing a cross is helpful in many applications where we need to obtain paired combinations of records.

 SELECT *
  FROM CUSTOMER
  CROSS JOIN ORDERS;


    6. Self Join

A self join is a regular join, but the table is joined with itself.

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

T1 and T2 are different table aliases for the same table.


I. Database View -imp


- Why we use view – to store complex query

Select * from student where roll = 101 and city = “Nagpur” and Percent > 60 and cast = “obc” or total >250 and total < 350 and sem = 1 and branch = “cse” and session = “23-24” and objective =”A” and section = “D”;

View name = report

- View Is called virtual table
- View store only for query result
- View is directly communicate to entity



There are following operation perform in a view

1. Create view
2. Update view
3. Delete view

View operator – As

Syntax :
Create view viewname AS query

Ex.  
Create view myview AS Select * from Demo2;



How to display view

select * from myview;
select * from myview where id=2;



How to update table
Syntax:
Alter view viewname As query

Ex.
Alter view my1 As Select * from Demo


Delete view
Syntax :
Drop view viewname;

Ex.
Drop view myview;



J. Store Procedure - imp

    - Procedure is a collection of query logic
    - Procedure is directly communicate to .net framework (aspx page)

How to Create Procedure

Syntax :
    Create Procedure procedurename
    AS
    BEGIN
        //create all logic
    END;


There are two method of procedure
1. Create procedure
2. Alter procedure


How to Create Procedure

Steps : Store procedure folder in server Explorer > right click > Add new Store Procedure
Sql server-
Database > program ability > store procedure


CREATE PROCEDURE mypro
AS
BEGIN
SELECT * FROM Demo3;
END;


How to Execute Procedure

EXEC procedurename;
EXECUTE procedurename;
EXEC mypro;



Categories of Procedure

1. Non Parameterized Procedure
2. Parameterized Procedure



Parameterized Procedure
- All types of parameter inside the procedure is use to @ operator

EX.
    CREATE PROCEDURE mypro2 (@name1 AS VARCHAR(50))
    AS
    BEGIN
    SELECT * FROM student WHERE name = @name1;
    END;



CREATE PROCEDURE mypro2(@name1 AS VARCHAR(50))
AS
BEGIN
    Select * from join_inventry where name = @name1;
END;



How to pass parameter in a procedure

Syntax :
    EXEC procedurename Parametervalue;
Ex.
    EXEC mypro2 ‘tv’;

K. (DCL) Data Control Language

DCL deals with the access and permissions to the data within the database. It is used to control who can access the database, perform operations, and make changes to data and structures.

Examples:
  • GRANT: Provides specific privileges to a user or role.
  • REVOKE: Removes specific privileges from a user or role.

GRANT SELECT, INSERT ON employees TO hr_user;

In summary, DML is focused on data manipulation (SELECT, INSERT, UPDATE, DELETE), DDL is focused on defining database structures (CREATE, ALTER, DROP), and DCL is concerned with access control and permissions (GRANT, REVOKE). These distinctions help in organizing and categorizing SQL commands based on their functionalities.

Interview Questions

1. Union vs Union All

When comparing UNION vs. UNION ALL, there is one major difference:

  • UNION only returns unique
  • UNION ALL returns all records, including duplicates.

Union

UNION combines the result set of two or more SELECT statements, showing only distinct values.

The SQL syntax below shows a UNION occurring between two different tables; the columns in both SELECT statements are of the same or matching data types.

The WHERE clause shown is an optional inclusion:

SELECT column_1, column_2
FROM table_1
[WHERE condition]
 
UNION
 
SELECT column_1, column_2
FROM table_2
[WHERE condition]


Union All

Remember, UNION ALL combines the results of two or more SELECT statements, showing all values, including duplicates if they exist.

The SQL syntax below shows a UNION ALL occurring between two different tables. Once again, the columns in the SELECT are of the same or matching data types and the WHERE clause is optional:


SELECT column_1, column_2
FROM table_1
[WHERE condition]
 
UNION ALL
 
SELECT column_1, column_2
FROM table_2
[WHERE condition]




Comments

Post a Comment

Popular posts from this blog

Web Application Development (Dot Net)

Image
Web Application Development Topics :  01. Dotnet Development Area 02. Page Creation concept 03. Types of Page in dotnet 04. Page Identification in dotnet 05. Server identification - imp 06. Directory identification - imp 07. URL format – most imp 08. How to create first web Application in dotnet framework 09. Dot net Environment 10. How to create first webpage in dotnet 11. Dotnet support which type of coding 12. How to clear control 13. How to set cursor in a control 14. Dot net color library drawing 15. Dot Net Navigation           15.1 Using Control           15.2 Navigation using code                     15.2.1 Redirect method                     15.2.2 Transfer method 16. Date and Time Concept 17. Content management in dotnet - imp 18. Property of Multiview 19. Dropdown list control 20. How...
Read more

C# Programming

Image
 Dotnet Framework Topics : 01. Dotnet Environment 02. Console Application 03. What is programming 04. Compiler 05. Interpreter 06. types of compiler 07. C# statement 08. C# function 09. How to create function 10. How to access function 11. Function parameter 12. User input 13. How to create multiple parameter 14. Named parameter 15. Unnamed parameter 16. Local and global variable in function 17. Function Methodology 18. Container variable 19. How to return multiple value in function 20. How to create tuple 21. How to return multiple value using tuple 22. Default Argument 23. C# Mathematical Functions 24. C# Arrays 25. Types of Allocation 26. Array Property 27. Array Separator 28. Error Representation 29. How to Create Array in C# 30. How to Store data element in Array 31. Types of loop in Array 32. Array Initialization 33. Array Methods 34. Array Sorting Using sort() 35. For Each 36. Double Dimensional Array 37. C# Structure 38. How to access structure members 39. Types o...
Read more