SQL PREPARATION TIME NOTE 3

As discussed in previous Notes ( i.e. NOTE 1 and NOTE 2 ), SQL PREPARATION TIME NOTE 3 article also helps you to get accurate knowledge. It's a less time-consuming and effective way in preparation time.

Here, Considering same Teacher table and Teacher_Account table. So, You can get clear knowledge.

SHORT TIME PREPARATION NOTE

ID	DESCRIPTION	FIRST_NAME	LAST_NAME	EMAIL	PHONE
1	DBMS	Alfreds	kuster	alfredskuster@yahoo.com	572222132
2	C++	Tom	Riddle	Tomriddle@yahoo.com	454522132
3	DBMS	Harry	Jonathan	harryJonathan@yahoo.com	258656854
4	PYTHON	Enrique	harn	enriqueharn@yahoo.com	452536425
5	C++	Kelvin	Mitnick	kelvinMitnick@yahoo.com	453656854
6	PYTHON	Michael	calce	Michaelcalce@yahoo.com	487656854
7	–	John	kart	Johncart@yahoo.com	654256854

Teacher Table

SQL LIKE & NOT LIKE OPERATOR

Different from real life LIKE or DISLIKE cases, you can use the LIKE operator in order to get some records but you remember only some specific pattern. The LIKE operator is used with the WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator: The percent sign( % ) represents zero, one, or multiple characters. The underscore ( _ ) represents a single character.

The "%" or "_" signs can be used in both situations i.e. before and after the pattern. The "%" represents Starting from a specific value or ending with a specific value. Whereas, The "_" representation is based on letter wise, let's take an example, 2nd character is 'a', then the string is: "_a%".

Try once, When Column value = Null is provided.
Suggestion

Syntax:

SELECT column_name1,..,column_nameN FROM table_name WHERE column_nameK LIKE pattern;
Example:
select * from teacher where  description like 'PY%' or phone like '6%';

ID	DESCRIPTION	FIRST_NAME	LAST_NAME	EMAIL	PHONE
4	PYTHON	Enrique	harn	enriqueharn@yahoo.com	452536425
6	PYTHON	Michael	calce	Michaelcalce@yahoo.com	487656854
7	-	John	kart	Johncart@yahoo.com	654256854

Like operator Output

NOT LIKE OPERATOR

Using the NOT keyword before the LIKE clause, you can get the opposite result of that LIKE to do. You can use it for not matching patterns.

Syntax:

SELECT column_name1,..,column_nameN FROM table_name WHERE column_nameK NOT LIKE pattern;
Example:
select * from teacher where phone not like '%4';

ID	DESCRIPTION	FIRST_NAME	LAST_NAME	EMAIL	PHONE
1	DBMS	Alfreds	kuster	alfredskuster@yahoo.com	572222132
2	C++	Tom	Riddle	Tomriddle@yahoo.com	454522132
4	PYTHON	Enrique	harn	enriqueharn@yahoo.com	452536425

Output of NOT LIKE

SQL SPECIAL SYMBOL

SQL SPECIAL SYMBOL means that there is some character used for string matching or searching purposes. The string can be of any type like phone no, emails, description, and so on. These characters are used with the SQL LIKE operator.

The [charlist] is Sets and ranges of characters to match.

The [^charlist] or [!charlist] is Matches only a character NOT specified within the brackets.

Syntax:

SELECT * FROM table_name WHERE columnK LIKE '[charlist]%'; 
SELECT * FROM table_name WHERE columnK LIKE '[!charlist]%';

The following first SQL command selects all values within a Columnk starting with char listed characters. The following second SQL command selects all values within a Columnk not starting with char listed characters.

SQL IN OPERATOR

The IN operator is a shorthand for multiple OR conditions. It is used with the WHERE clause.

Syntax:

SELECT * FROM table_name WHERE column_nameK IN (value1,value2,...);
Example:
select * from teacher where id in (5,4);

ID	DESCRIPTION	FIRST_NAME	LAST_NAME	EMAIL	PHONE
4	PYTHON	Enrique	harn	enriqueharn@yahoo.com	452536425
5	C++	Kelvin	Mitnick	kelvinMitnick@yahoo.com	453656854

Output of IN operator

SQL BETWEEN & NOT BETWEEN OPERATOR

In the case of considering text or numeric datatype value, specific value, or matching for pattern, You can use LIKE, or IN operator. BUT, In the case of considering all values within two given values then BETWEEN operator is useful. This operator comes with BETWEEN & NOT BETWEEN OPERATOR. The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

Syntax:

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
Example:
select * from teacher where id between 4 and 5;

ID	DESCRIPTION	FIRST_NAME	LAST_NAME	EMAIL	PHONE
4	PYTHON	Enrique	harn	enriqueharn@yahoo.com	452536425
5	C++	Kelvin	Mitnick	kelvinMitnick@yahoo.com	453656854
6	PYTHON	Michael	calce	Michaelcalce@yahoo.com	487656854

Output of Between

The value1 should be smaller than value2. Between Operator comes with AND operator does not come with OR operator. If values are text then put it into a single quotation mark.

SQL NOT BETWEEN OPERATOR

This is the opposite case of BETWEEN operator. In order to consider outside range values, you can apply the NOT BETWEEN operator.

If the value1 is greater than the value2 then NOT BETWEEN operator can work abnormally.

Syntax:

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
Example:
select * from teacher where id not between 1 and 5;

ID	DESCRIPTION	FIRST_NAME	LAST_NAME	EMAIL	PHONE
6	PYTHON	Michael	calce	Michaelcalce@yahoo.com	487656854
7	-	John	kart	Johncart@yahoo.com	654256854

NOT BETWEEN OPERATOR

As SQL stands for Structured Query Language, Different types of structure can be constructed, some of useful constructions are given below

SQL BETWEEN WITH IN OPERATOR

The following SQL statement select all Ids between 4 to 7 but not displayed Description="PYTHON".

SELECT * FROM teacher WHERE (Id BETWEEN 4 AND 7) AND (NOT DESCRIPTION  IN ('PYTHON','Null'));

ID	DESCRIPTION	FIRST_NAME	LAST_NAME	EMAIL	PHONE
5	C++	Kelvin	Mitnick	kelvinMitnick@yahoo.com	453656854

BETWEEN WITH IN OPERATION

In some databases, BETWEEN selects fields that are between and excluding the test values.
In other databases, BETWEEN selects fields that are between and including the test values.
And in other databases, BETWEEN selects fields between the test values, including the first test value and excluding the last test value.

SQL ALIASES or RENAME

In a big database related query every field has some unique Names may be longer or shorter. You can rename those fields even tables in a short form using SQL ALIASES or SQL RENAME feature.

Rename of table or fields is temporary. It is created manually, not inbuilt.

Syntax:

// Column related Alias
SELECT column_name AS alias_name FROM table_name;
// Table related Alias
SELECT * FROM table_name AS alias_name;
Example: 
select id as I from teacher as T;

The As clause is used for temporarily rename a column or Table. It improves output readability. Both cases can be applied together but always remember that when applied in both cases, Different alias names have to be used.

In some database systems, If you want to rename a column with some name that contains spaces then use either double quotation marks or square brackets. It does not work with tables.

In some database, Rename can be done with more than one columns together.

Syntax:

SELECT Column1, Column2+', '+Column3+', '+Column4+', '+Column5 AS NEW_NAME FROM Table_name;
SELECT Column1, CONCAT(Column2,', ',Column3,', ',Column4,', ',Column5 ) AS NEW_NAME FROM Table_name;

Both of the syntax is works depends on DBMS system.

CONCAT is function in SQL used to concatenate.

You can see another table named as Teacher_account is given below.

ID	TEACHER_ID	LOGIN	PASWORD
1	1	alfredskuster@yahoo.com	alfredskuster
2	3	harryJonathan@yahoo.com	harryJonathan
3	2	Tomriddle@yahoo.com	Tomriddle

Teacher_account Table

ID	DESCRIPTION	FIRST_NAME	LAST_NAME	EMAIL	PHONE
1	DBMS	Alfreds	kuster	alfredskuster@yahoo.com	572222132
2	C++	Tom	Riddle	Tomriddle@yahoo.com	454522132
3	DBMS	Harry	Jonathan	harryJonathan@yahoo.com	258656854
4	PYTHON	Enrique	harn	enriqueharn@yahoo.com	452536425
5	C++	Kelvin	Mitnick	kelvinMitnick@yahoo.com	453656854
6	PYTHON	Michael	calce	Michaelcalce@yahoo.com	487656854
7	-	John	kart	Johncart@yahoo.com	654256854

Teacher Table

Rename the Teacher table as T and Teacher_account table as TA, we can easily use T.ID, T.DESCRIPTION, T.FIRST_NAME, T.LAST_NAME, T.EMAIL, T.PHONE, and TA.ID, TA.Teacher_ID, TA.LOGIN, TA.PASWORD when joining.

Example:

Select T.ID, T.First_name, TA.Login  from Teacher T , Teacher_account TA where T.ID=TA.Teacher_ID;

This SQL command is easy to read and understand rather than the given below SQL command.

Select Teacher.ID, Teacher.First_name, Teacher_Account.Login  from Teacher , Teacher_Account where Teacher.ID=Teacher_Account.Teacher_ID;

ID	FIRST_NAME	LOGIN
1	Alfreds	alfredskuster@yahoo.com
3	Harry	harryJonathan@yahoo.com
2	Tom	Tomriddle@yahoo.com

Output of Joining

Aliases can be useful when :

There are more than one table involved in a query
Functions are used in the query
Column names are big or not very readable
Two or more columns are combined together

FAQ:

DOES 'NULL' is same as NULL value?

There are huge differences between these two. Null is not a value. Null is inserted in those portions where no value is specified. In optional fields, Null is saved as a part of data that can be modified later.

If, you inserted data 'Null' as value. You can apply 'Null' to catch them. As Teacher ID=7 has no Description, thus it is null by default.

insert into teacher values (8,'Null','A','B','asda','133331');
select * from teacher where description='Null';

ID	DESCRIPTION	FIRST_NAME	LAST_NAME	EMAIL	PHONE
8	Null	A	B	asda	133331

Output of 'Null'

Within the Quotation, Text is Case-sensitive. Null is described in this article.

If no value is inserted or leave single quote as blank ( '' ), then it is considered as Null. To catch them, You can apply "Column_name is Null" operation.

Syntax:

SELECT * FROM table_name WHERE column_name IS NULL; 
Example:
select * from teacher where description is Null;

ID	DESCRIPTION	FIRST_NAME	LAST_NAME	EMAIL	PHONE
7	-	John	kart	Johncart@yahoo.com	654256854

Null value fetched using IS operator

The IS NULL operator is used to test for empty values (NULL values). Always use IS NULL to look for NULL values.

Similarly Null, We can fetch NOT NULL values. The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).

Syntax:

SELECT * FROM table_name WHERE column_name IS NOT NULL; 
Example:
select * from teacher where description is not Null;

ID	DESCRIPTION	FIRST_NAME	LAST_NAME	EMAIL	PHONE
1	DBMS	Alfreds	kuster	alfredskuster@yahoo.com	572222132
2	C++	Tom	Riddle	Tomriddle@yahoo.com	454522132
3	DBMS	Harry	Jonathan	harryJonathan@yahoo.com	258656854
4	PYTHON	Enrique	harn	enriqueharn@yahoo.com	452536425
5	C++	Kelvin	Mitnick	kelvinMitnick@yahoo.com	453656854
6	PYTHON	Michael	calce	Michaelcalce@yahoo.com	487656854
8	Null	A	B	asda	133331

NOT NULL Values over Description. Thus, Id=7 record is not selected

Search This Blog

DBMS