Online Training

What is a subquery in SQL?

SubQuery is a query within another query. The outer query is called as main query and inner query is called as SubQuery.

The SubQuery generally executes first, and its output is used to complete the query condition for the main or outer query. Subquery must be enclosed in parentheses.

Subqueries are on the right side of the comparison operator. You can place the Subquery in a number of SQL clausesWHERE clauseHAVING clauseFROM clause.

Subqueries can be used with SELECTUPDATEINSERTDELETE statements along with expression operator. It could be equality operator or comparison operator such as =, >, =, <= and Like operator.

ORDER BY command cannot be used in a SubQueryGROUPBY command can be used to perform same function as ORDER BY command.

A Sample Syntax Example for SubQuery:

SELECT column_name

FROM table_name

WHERE column_name expression operator

( SELECT COLUMN_NAME from TABLE_NAME WHERE … );

SQL Server Online Training

SQL LIKE Operator

The LIKE operator is used to list all rows in a table whose column values match a specified pattern. It is useful when you want to search rows to match a specific pattern, or when you do not know the entire value. For this purpose we use a wildcard character ‘%’.

For example: To select all the students whose name begins with ‘S’

SELECT first_name, last_name
FROM student_details
WHERE first_name LIKE ‘S%’;

The output would be similar to:

first_name          last_name
————-          ————-
Stephen                 Fleming
Shekar                   Gowda

The above select statement searches for all the rows where the first letter of the column first_name is ‘S’ and rest of the letters in the name can be any character.

What is a NULL Value?

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

Note: It is very important to understand that a NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

How to Connect to SQL Server after Installation

This video explains on “How to Connect to SQL Server after Installation”

Applicable for SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 and SQL Server 2016

T0 resolve possible connection issues at basic level, ensure the following:

1. Verify and ensure correct server name is provided

2. Go to Run then services.msc then ensure SQL Server service for the respective instance is started.

Any clarifications: Please email to contact@sqlschool.com

For Real-time Trainings and Projects, Placements on SQL Server, please visit http://www.sqlschool.com

Or call us on +91 9666440801 (INDIA) or +1 510-400-4845 (USA/CANADA)

What is the difference between SQL Server and SQL?

SQL stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. It is used to perform operations on the data present in database. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as “Select”, “Insert”, “Update”, “Delete”, “Create”, and “Drop” can be used to accomplish almost everything that one needs to do with a database. SQL commands are divided into several different types, among them data manipulation language (DML) and data definition language (DDL) statements, transaction controls and security measures.

Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.

SQL Server is a relational database management system (RDBMS) developed by Microsoft. This product is built for the basic function of storing retrieving data as required by other applications. It can be run either on the same computer or on another across a network. It is platform dependent. It is both GUI and command based software.

SQL Server is available in various editions. Some of the editions are:

  • Enterprise
  • Standard
  • Workgroup
  • Web
  • Developer
  • Express
  • Compact
  • Datacenter
  • Business Intelligence
  • Enterprise Evaluation

So, different enterprises like web development companies, mobile app development companies, banks, outsourcing companies and many other uses RDBMS like (SQL server, Oracle) for smooth functioning of their business.

Using of And, OR and NOT in SQL Server

AND: The AND operator displays a record if all the conditions separated by AND is TRUE.

Syntax:

SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;

Example:

SELECT * FROM Customers
WHERE Country=’India’ AND City=’Hyderabad’;

The above SQL statement selects all fields from “Customers” where country is “India” AND city is “Hyderabad”

OR:The OR operator displays a record if any of the conditions separated by OR is TRUE.

Syntax:

SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2 OR condition3 …;

Example:

SELECT * FROM Customers
WHERE City=’Hyderabad’ OR City=’Pune’;

The following SQL statement selects all fields from “Customers” where city is “Hyderabad” OR “Pune”

NOT:The NOT operator displays a record if the condition(s) is NOT TRUE.

Syntax:

SELECT column1, column2, …
FROM table_name
WHERE NOT condition;

Example:

SELECT * FROM Customers
WHERE NOT Country=’India’;

Date data types

DATE() A date. Format: YYYY-MM-DD

Note: The supported range is from ‘1000-01-01’ to ‘9999-12-31’

DATETIME() A date and time combination. Format: YYYY-MM-DD HH:MI:SS

Note: The supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’

TIMESTAMP() A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC). Format: YYYY-MM-DD HH:MI:SS

Note: The supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’

TIME() A time. Format: HH:MI:SS

Note: The supported range is from ‘-838:59:59’ to ‘838:59:59’

YEAR() A year in two-digit or four-digit format.

Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

Second Normal Form(2nd) (Eliminating Redundant Data)

A relation is in second normal form if it is in 1NF and every non key attribute is fully functionally dependent on the primary key.

It is in first normal form
All non-key attributes are fully functional dependent on the primary key

The First Normal form deals with the atomicity whereas the Second Normal Form deals with the relationship between the composite key columns and non-key columns.

To achieve the next progressive level your table should satisfy the requirement of First Normal Form then move towards the Second Normal Form.

 Let see the example from above 1st Normal From

ITEM COLORS PRICES TAX
Pen Red 3.0 0.40
Pen Blue 3.0 0.40
Scale Red 3.0 0.40
Scale Yellow 3.0 0.40
Bag Blue 120.0 12.52
Bag Black 120.0 12.52

Table is not in Second Normal Form because the price and tax depends on the item, but not color.

ITEM COLORS
Pen Red
Pen Blue
Scale Red
Scale Yellow
Bag Blue
Bag Black

 

ITEM PRICE TAX
Pen 3.0 0.40
Scale 3.0 0.40
Bag 120.0 12.52

 

How to use INNER JOIN in SQL Server?

The most frequently used and important of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Syntax: The basic syntax of INNER JOIN is as follows:

SELECT table1.column1, table2.column2…
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Image Representation:

The INNER JOIN in SQL joins two tables according to the matching of a certain criteria using a comparison operator.

Sample Two Table Data:

FOODS:

 ITEM_ID ITEM_NAME ITEM_UNIT COMPANY_ID
1 Bread PCS 25
2 Sliced Bread PCS 16
3 Roasted Bread PCS 32
4 Garlic Bread PCS 48
5 Cheese Bread PCS 18
6 Fruit Bread PCS 22
7 Milk Bread PCS 28

Company:

COMPANY_ID COMPANY_NAME COMPANY_CITY
25 SUBWAY IRLAND
16 BURGER KING UNITED STATES
32 KFC UK
18 ROLLZONE AUSTRALIA
28 DOMINOS INDIA

Example

Here is an example of inner join in SQL between two tables.

To join item name, item unit columns from foods table and company name, company city columns from company table, with the following condition –

1. company_id of foods and company_id company table must be same, the following SQL statement can be used :

SELECT foods.item_name,foods.item_unit,
company.company_name,company.company_city
FROM foods
INNER JOIN company
ON foods.company_id =company.company_id;

Example of SQL INNER JOIN using JOIN keyword

To get item name, item unit columns from foods table and company name, company city columns from company table, after joining these mentioned tables, with the following condition –

1. company id of foods and company id of company table must be same,

the following SQL statement can be used :
SELECT foods.item_name,foods.item_unit,
company.company_name,company.company_city
FROM foods
JOIN company
ON foods.company_id =company.company_id;

SQL Server next version CTP 1.4

Microsoft is excited to announce a new preview for the next version of SQL Server (SQL Server v.Next). Community Technology Preview (CTP) 1.4 is available on both Windows and Linux. In this preview, they added the ability to schedule jobs using SQL Server Agent on Linux. We can try the preview in your choice of development and test environments now: www.sqlserveronlinux.com.

Key CTP 1.4 enhancements
The primary enhancement to SQL Server v.Next on Linux in this release is the ability to schedule jobs using SQL Server Agent. This functionality helps administrators automate maintenance jobs and other tasks, or run them in response to an event. Some SQL Server Agent functionality is not yet enabled for SQL Server on Linux. To learn more and see sample SQL Server Agent jobs, you can read detailed blog titled “SQL Server on Linux: Running scheduled jobs with SQL Server Agent” or attend an Engineering Town Hall about “SQL Server Agent and Full Text Search in SQL Server on Linux.”

The mssql-server-linux container image on Docker Hub now includes the sqlcmd and bcp command line utilities to make it easier to create and attach databases and automate other actions when working with containers. For additional detail on CTP 1.4, please visit What’s New in SQL Server v.Next, Release Notes and Linux
documentation.

In addition, SQL Server Analysis Services and SQL Server Reporting Services developer tools now support Visual Studio 2017. They are available for installation from the Visual Studio Marketplace providing the option for automatic updates going forward.

 

Get SQL Server v.Next CTP 1.4 today!
Try the preview of the next release of SQL Server today! Get started with the preview of SQL Server with our developer tutorials that show you how to install and use SQL Server v.Next on macOS, Docker, Windows, and Linux and quickly build an app in a programming language of your choice.