SQL JOINS | LEARN SQL

HEY GIRUILLIANS WELCOME BACK TO OUR SQL SERIES WE HAVE POSTED 4 BLOGS ABOUT AND THIS IS 6TH IF YOU ARE NEW PLEASE READ PREVIOUS BLOGS TO LEARN WITH FLOW LET’S START

IN THE PREVIOUS CLASS WE HAD LEARNT ABOUT

A. SQL ORDER BY FUNCTIONS:

• ORDER BY CLAUSE

• ORDER BY ASC

• ORDER BY DESC

• ORDER BY RANDOM

• ORDER BY MULTIPLE COLUMNS

TODAY WE WILL LEARN ABOUT

A. SQL JOINS

• SQL Outer Join

• LEFT OUTER JOIN (or LEFT JOIN)

• RIGHT OUTER JOIN (or RIGHT JOIN)

• FULL OUTER JOIN

• LEFT JOIN (IF NULL)

• SQL Cross Join

• SQL KEYS

• PRIMARY KEY

• FOREIGN KEY

• COMPOSITE KEY

• UNIQUE KEY

• ALTERNATE KEY

TABLES IN USE

TABLE 1 : GUERILLATECK

TABLE 2: GUERILLA CARS

1. SQL JOINS

In SQL, joins are used to combine rows from two or more tables based on related columns. Joins allow you to retrieve data from multiple tables in a single query, enabling you to establish relationships and extract meaningful information from your database.

2. SQL OUTER JOIN

In SQL, an OUTER JOIN is a type of join that includes unmatched rows from one or both tables involved in the join. It allows you to retrieve records even when there is no match between the joining columns in the tables.

There are three types of OUTER JOIN in SQL:

• LEFT OUTER JOIN (or LEFT JOIN):

Returns all the rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for the right table columns.  

 

SYNTAX IS AS FOLLOWS:-

SELECT column1, column2, ...

FROM table1

LEFT JOIN table2 ON table1.column = table2.column;

STRUCTURE FOR LEFT OUTER JOIN

FOR EXAMPLE

OUTPUT:

• RIGHT OUTER JOIN:-

Returns all the rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for the left table columns.

 

SYNTAX IS AS FOLLOWS:-

SELECT column1, column2, ...

FROM table1

RIGHT JOIN table2 ON table1.column = table2.column;

STRUCTURE FOR RIGHT OUTER JOIN

For example

Output

NOTE*- Outer joins are useful when you want to include all records from one table, regardless of whether there is a match in the other table. They are typically used when you need to join tables based on non-matching or optional relationships. By including unmatched rows, you can still retrieve relevant data and perform analysis or reporting.

• LEFT JOIN (IF NULL)

In SQL, you can use a LEFT JOIN and the IS NULL condition to retrieve records from the left table that do not have a matching record in the right table. This allows you to handle situations where you want to perform specific actions or display certain values when there is no match in the joined table.

 

SYNTAX IS AS FOLLOWS:-

SELECT column1, column2, ...

FROM table1

LEFT JOIN table2 ON table1.column = table2.column

WHERE table2.column IS NULL;

Structure of LEFT JOIN (IF NULL)

For example

Output

There are no null values so no entries are displayed

• RIGHT JOIN (IF NULL)

In SQL, you can use a RIGHT JOIN and the IS NULL condition to retrieve records from the right table that do not have a matching record in the left table. This allows you to handle situations where you want to perform specific actions or display certain values when there is no match in the joined table.

The syntax is as follows:-

SELECT column1, column2, ...

FROM table1

RIGHT JOIN table2 ON table1.column = table2.column

WHERE table1.column IS NULL;

Structure of Right JOIN (IF NULL)

For example:

Output

There are no null values so no entries are displayed

• SQL CROSS JOIN 

In SQL, a CROSS JOIN, also known as a Cartesian join, is used to combine every row from the first table with every row from the second table. It does not rely on any matching condition between the tables.

 

The syntax for a CROSS JOIN is as follows:

SELECT column1, column2, ...

FROM table1

CROSS JOIN table2;

THE STRUCTURE IS AS FOLLOWS

Output:-

• SQL KEYS:

In SQL, keys are used to identify and establish relationships between tables in a database. They help ensure data integrity, enforce uniqueness, and facilitate efficient data retrieval. There are several types of keys commonly used in SQL:

a. SQL PRIMARY KEYS

A primary key is a unique identifier for each record in a table. It ensures that each row in the table is uniquely identified and allows for quick and efficient retrieval of specific records. A primary key cannot contain duplicate or NULL values.

b. SQL FOREIGN KEY

A foreign key establishes a relationship between two tables by referring to the primary key of another table. It ensures referential integrity, meaning that the values in the foreign key column must match the values in the primary key column of the referenced table or be NULL. Foreign keys are used to enforce data integrity and maintain relationships between related tables.

 

c. SQL FOREIGN KEY  

A foreign key establishes a relationship between two tables by referring to the primary key of another table. It ensures referential integrity, meaning that the values in the foreign key column must match the values in the primary key column of the referenced table or be NULL. Foreign keys are used to enforce data integrity and maintain relationships between related tables.

 

d. SQL UNIQUE KEY 

  

A unique key enforces uniqueness on one or more columns in a table, similar to a primary key. However, unlike a primary key, a unique key can allow NULL values. Each unique key constraint ensures that the values in the specified column(s) are unique across the table.

e. SQL ALTERNATE KEY

In SQL, an alternate key refers to a candidate key that is not selected as the primary key for a table. It is a unique identifier for each record, just like a primary key, but it is not designated as the primary means of identification. 

  

While a primary key is chosen as the primary identifier for a table, an alternate key provides an additional unique identifier. This can be useful in scenarios where multiple unique identifiers are required or when there are multiple columns or combinations of columns that can uniquely identify a record.

SQL SORT FUNCTION | LEARN SQL

HEY GIRUILLIANS WELCOME BACK TO OUR SQL SERIES WE HAVE POSTED 4 BLOGS ABOUT AND THIS IS THE 6TH IF YOU ARE NEW PLEASE READ PREVIOUS BLOGS TO LEARN WITH FLOW LET’S START.

IN THE PREVIOUS CLASS WE HAD LEARNT ABOUT

1.SQL CLAUSES:

• SQL WHERE

• SQL AND

• SQL OR

• SQL AS

• SQL HAVING

TODAY WE WILL LEARN ABOUT

A)SQL ORDER BY FUNCTIONS:

• ORDER BY CLAUSE

• ORDER BY ASC

• ORDER BY DESC

• ORDER BY RANDOM

• ORDER BY MULTIPLE COLUMNS

SO GUYS LET’S START

QUES. WHAT IS SQL ORDER BY FUNCTIONS?

ANS. The result set can be sorted ascending or descending using the ORDER BY keyword.

NOTE: Records are sorted in ascending order by default when the ORDER BY keyword is used. Use the DESC keyword to sort the records so that they are in descending order.

Here, you specify the columns you want to retrieve from the table in the SELECT clause and then use the ORDER BY clause to limit the columns you want to sort the result set. You can also specify the sorting order for each column using the optional ASC (ascending) or DESC (descending) keyword. By default, if you do not specify an order, it will be sorted in ascending order.

a)ORDER BY CLAUSE

The result set of a SELECT query can be sorted in ascending or descending order using one or more columns by using the ORDER BY clause in SQL. The following is how to format the ORDER BY clause:

SELECT column1, column2, ...

FROM table_name

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

LET TABLE:-GUERILLATECK

FOR EXAMPLE:

Note that ASC is the default sorting order if you don't specify either ASC or DESC keyword in the ORDER BY clause.

OUTPUT:-

IT WILL SORT NAMES BY ASCENDING ORDER

b) Order By Ascending:-

ORDER BY ASC is used in SQL to sort the result set of a SELECT query in ascending order. When you use ASC in the ORDER BY clause, it sorts the result set from the lowest value to the highest value based on the specified column or expression.

The basic syntax of using ORDER BY ASC is as follows:

SELECT column1, column2, ...

FROM table_name

ORDER BY column1 ASC, column2 ASC, ...;

Here, you specify the columns you want to retrieve from the table in the SELECT clause and then use the ORDER BY clause to limit the columns you want to sort the result set. You can also specify the sorting order for each column using the ASC keyword.

For example:-

Output:-

This will return all employees in the table, sorted by their salary in ascending order. If two or more employees have the same salary, they will be sorted based on their EmployeeID in ascending order as well.

Note that ASC is the default sorting order if you don't specify either ASC or DESC keyword in the ORDER BY clause.

c) Order By Descending

ORDER BY DESC is used in SQL to sort the result set of a SELECT query in descending order. When you use DESC in the ORDER BY clause, it sorts the result set from the highest value to the lowest value based on the specified column or expression.

The basic syntax of using ORDER BY DESC is as follows:

SELECT column1, column2, ...

FROM table_name

ORDER BY column1 DESC, column2 DESC, ...;

Here, you specify the columns you want to retrieve from the table in the SELECT clause and then use the ORDER BY clause to limit the columns you want to sort the result set. You can also specify the sorting order for each column using the DESC keyword.

Output:-

This will return all entries in the table, sorted by their salary in descending order. If two or more employees have the same salary, they will be sorted based on their names in ascending order.

Note that DESC is the opposite sorting order of ASC, and it reverses the sorting direction of the specified column or expression.

d) ORDER BY RANDOM

To order the result set in a random order, you can use the RAND() function in the ORDER BY clause of a SQL SELECT statement. The RAND() function generates a random number for each row in the result set, and then the rows are sorted based on these random numbers.

The basic syntax for using ORDER BY RAND() is as follows:

SELECT column1, column2, ...

FROM table_name

ORDER BY RAND();

THIS FUNCTION NOT WORK IN THE LATEST VERSIONS

Here, you specify the columns that you want to retrieve from the table in the SELECT clause, and then you use the ORDER BY clause to order the result set in a random order using the RAND() function

e) Order by multiple columns

You can sort the result set by multiple columns in SQL by specifying each column in the ORDER BY clause. The sorting order for each column can be specified separately using the ASC or DESC keyword.

The basic syntax of using ORDER BY with multiple columns is as follows:

SELECT column1, column2, ...

FROM table_name

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

For example:-

Output:-

This will return all entries in the table, sorted by name in ascending order first and then by class in descending order within each department. If two or more employees have the same salary within a department, they will be sorted based on their id in ascending order.

So that’s it for today guys if you want to learn more, so follow us...

SQL WHERE, HAVING, AND, OR, AS CLAUSES | LEARN SQL

HEY GIRUILLIANS WELCOME BACK TO OUR SQL SERIES WE HAD POSTED 4 BLOGS ABOUT AND THIS IS 6TH IF YOU ARE NEW PLEASE READ PREVIOUS BLOGS TO LEARN WITH FLOW LET’S START

IN THE PREVIOUS CLASS WE HAD LEARNT ABOUT

1. ALTER COMMAND

2. SQL COPY TABLE

3. DISTINCT FUNCTION

4. TYPES OF SELECT FUNCTION

Types of command in sql part 2 | LEARN SQL

Hey Guriilians welcome to the continuing series of SQL in previous blogs we learned about the types of commands in SQL, select command, where clause, types of keys, identifiers keywords, and views If you haven’t read about that you can read it now too.

Let’s continue with our topic:-

Today we’ll be studying these topics

1. ALTER COMMAND

2. SQL COPY TABLE

3. DISTINCT FUNCTION

4. TYPES OF SELECT FUNCTIONS

ALTER COMMANDS:-

In SQL, the ALTER command is used to modify the structure of an existing database object, such as a table, view, or index.

ALTER TABLE: This command is used to modify the structure of a table. You can use this command to add, modify, or delete columns in a table, or to change the data type or size of a column. The syntax for the ALTER TABLE command is:

ALTER TABLE table_name

action1,

action2,

...

FOR EXAMPLE:-

Table name=guerillateck

Output:-

Note*- When you add a column to the table so every value becomes null of the newly added column.

ALTER VIEW: This command is used to modify the definition of a view. You can use this command to change the SELECT statement that defines the view or to change the name of the view. The syntax for the ALTER VIEW command is:

ALTER VIEW view_name
AS new_select_statement;

For example:-

OUTPUT:-

ALTER INDEX: This command is used to modify the structure of an index. You can use this command to add or remove columns from an index, or to change the order of the columns in the index. The syntax for the ALTER INDEX command is:

ALTER INDEX index_name action1, action2, ...

SQL COPY TABLE:-

With the help of the SELECT INTO command in SQL, it is possible to transfer data from one SQL table to another SQL table on the same SQL server.

A table's contents are transferred into a new table using the SELECT INTO statement in Structured Query Language. Utilizing the structure of the original table, SQL builds a new table.

Syntax of SELECT INTO statement in SQL

SELECT * INTO New_table_name FROM old_table_name;

FOR EXAMPLE:-

OUTPUT

DISTINCT FUNCTION

In SQL, the DISTINCT function is used to retrieve unique or distinct values from a specified column or columns in a table. It eliminates duplicate rows from the result set of a SELECT statement.

Here is the basic syntax for using the DISTINCT function:

SELECT DISTINCT column_name
FROM table_name;

In this syntax, column_name is the name of the column from which you want to retrieve distinct values, and table_name is the name of the table from which you want to retrieve the data.

For example, suppose you have a table called guerillateck that contains the following data:

For example:-

Output:-

We had already learned about the select command in previous blog lets revise then we start with further types for select.

SQL SELECT Statement

The command in Structured Query Language that is used the most is the SELECT statement. The records from one or more database tables and views can be accessed using this method. Additionally, it retrieves the chosen data that adheres to our desired criteria.

By using this command, we can also obtain the specific record from the specific table column. The table that contains the record that the SELECT statement returned is referred to as a result-set table.

Syntax of SELECT Statement in SQL

SELECT Column_Name_1, Column_Name_2, ....., Column_Name_N FROM Table_Name;

In this SELECT syntax, Column_Name_1, Column_Name_2, ….., and Column_Name_N is the name of those columns in the table whose data we want to read.

If you want to access all rows from all fields of the table, use the following SQL SELECT syntax with * asterisk sign:

SELECT COUNT FUNCTION

In SQL, the SELECT COUNT function is used to count the number of rows in a table that meets certain conditions. It can be used with or without the WHERE clause to count all rows in a table or only those rows that meet specific criteria.

Here is the basic syntax for using the SELECT COUNT function:

SELECT COUNT(*) FROM table_name WHERE condition;

In this syntax, * is a wildcard that specifies all columns in the table, table_name is the name of the table that you want to count the rows from, and the condition is an optional clause that specifies one or more conditions that the rows must meet to be included in the count.

For example:-

This will show the no. of std in class 11 in table guerillateck.

Output

SQL SELECT TOP

The small number of records or rows from the database table is displayed using the SQL SELECT TOP statement. How many rows are returned is specified by the TOP clause in the query.

In the output, the top N rows from the tables are displayed. When the database tables contain thousands of records, this clause is used.

Syntax of TOP Clause in SQL

SELECT TOP number | percent column_Name1, column_Name2, ....., column_NameN FROM table_name WHERE [Condition] ;

Let table guerillacars

carname	carcolor	carcost
mercedes benz	white	800000
Hyundai Creta	white	1085000
Hyundai Venue	white	950000
Hyundai i20	Red	900000
Kia Sonet	white	900000
Kia Seltos	Black	8000000

Output

so that's it for today guys, follow us to learn more about SQL topics.....

happy learning.....

TYPES OF COMMAND IN SQL | LEARN SQL

Hey, in previous blogs we had learnt about the introduction, history, installation,  and creation updation, and deletion of tables or a database today we are continuing with the types of command

TYPES OF COMMAND IN SQL

1)Data Definition Language (DDL): Data Definition Language (DDL) is a set of SQL commands used to define the database structure or schema. DDL statements are used to create, modify, or delete database objects such as tables, indexes, views, sequences, and constraints.

Here are 2 common DDL commands we have learned to use in SQL:

• CREATE:  This command is used to create a new database object such as a table, view, index, or sequence.

• DROP: This command is used to delete an existing database object such as a table, view, index, or sequence.

2)Data Manipulation Language (DML): Data Manipulation Language (DML) is a set of SQL commands used to manipulate the data stored in a database. DML commands are used to insert, update, delete, and retrieve data from tables. Here are some common DML commands used in SQL:

• SELECT: Data can be retrieved from one or more tables using the SELECT command.You can specify the columns and rows to be returned using various clauses like WHERE, ORDER BY, GROUP BY, etc.

• INSERT: This command is used to insert new rows of data into a table. You can specify the columns and values to be inserted.

3)Data Control Language (DCL): Data Control Language (DCL) is a set of SQL commands used to control access to database objects. DCL commands are used to grant or revoke privileges to the users or roles.

4)Transaction Control Language (TCL): Transaction Control Language (TCL) is a set of SQL commands used to manage the transactions in the database. TCL commands are used to ensure the consistency and integrity of the data by controlling the transactions.

5)Session Control Commands: SQL does not have a set of specific "Session Control Commands" (SCC) as a standard part of its syntax. However, different database management systems (DBMS) may provide their own proprietary SCCs to manage database sessions. These SCCs can help control how users interact with the database, track usage and resource allocation, and manage the database connections and sessions.

Note*:- We hasn’t studied commands about Data Control Language (DCL) , Transaction Control Language (TCL), or Session Control Commands we’ll be studying it in further Blogs.

SELECT COMMAND IN SQL

Data can be obtained from one or more tables using the SELECT statement in SQL. In a SELECT statement, the following fundamental syntax is used:

SELECT column1, column2, ...

FROM table_name

WHERE COLUMN_NAME = value;

FOR EXAMPLE:-

OUTPUT:-

SELECT IN SQL

In SQL, the WHERE clause is used in a SELECT, UPDATE, or DELETE statement to filter the rows returned based on a specified condition. The basic syntax of a WHERE clause is as follows:

SELECT column1, column2, ...

FROM table_name

WHERE some_column = some_value;

FOR EXAMPLE:-

It will show students with stream commerce

KEYS IN SQL

In SQL, keys are used to uniquely identify rows in a table. There are three types of keys commonly used in SQL: primary keys, foreign keys, and unique keys.

• Primary Key: A column or group of columns known as a primary key uniquely identifies each row in a table.
  It is used to enforce entity integrity, which means that each row in the table represents a unique entity. Primary keys must be unique and cannot contain null values.

• Foreign Key: A column or group of columns that make reference to the primary key of another table are known as foreign keys. It is used to enforce referential integrity, which means that the values in the foreign key column(s) must match the values in the primary key column(s) of the referenced table. Foreign keys are used to establish relationships between tables.

• Unique Key: A unique key is a column or set of columns that uniquely identifies each row in a table, similar to a primary key. However, unlike a primary key, a unique key can contain null values. Unique keys are used to enforce entity integrity, just like primary keys.

THERE IS NO SYNTX FOR KEYS THESE WILL BE INSERTED AT THE TIME OF TABLE CREATION.

IDENTIFIER

The names of things like databases, tables, or columns are where identifiers are most frequently employed.

Rules for identifiers:-

* begin with a Unicode letter or an underscore (_). Letters, underscores, numerals (0–9), or dollar signs ($) are all acceptable as additional characters.

* keyword is not accepted as an identifier.

KEYWORDS

These are reserved words and can have specific meanings in statements to perform various operations in the database.

Views in SQL:

Views are database objects with no value; that's why it does not store physically. Views are a great tool for hiding or abstracting complex queries because of this characteristic.

Although it does not actually have rows and columns, it resembles one. Consequently, it can be said that its contents are based on the base table. Although it doesn't have any data of its own, it operates similarly to the base table. Its name is always unique, like tables. The views differ from tables as they are definitions that are created on top of other tables (or views). If any changes occur in the underlying table, the same changes are reflected in the views also.

Uses of views

Implementing the security mechanism is the main function of views in SQL Server. Specific columns and rows in tables are hidden from users by this. It only shows the data returned by the query that was declared when the view was created. The user is never made aware of the remaining details.

Types of views

1)User-Defined Views: Users define these views to meet their specific requirements.

2)System-Defined Views: System-defined views are pre-built, pre-existing views that are saved in SQL Server and include Tempdb, Master, and temp.

Syntax of creating views in sql:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

FOR EXAMPLE:-

OUTPUT:-

Rename views
SP_RENAME View_Old_Name, View_New_Name

Drop Views in SQL Server

DROP VIEW [IF EXISTS] schema_name.view_name;

DROP VIEW Student

so that's it for today guys, follow us to learn more about SQL topics.....

happy learning.....