Sql preparation time note 1

Sql preparation time note 1

This “SQL PREPARATION TIME NOTE 1” article, covers SQL, SQL Functionalities, RDBMS, select Distinct, select top, select, where, And or operator, order by, semicolon, numeric & text fields.

You can achieve a very high degree of knowledge in SQL in a very effective way and it’s less time-consuming.

There are many DATABASE SYSTEM i.e. MySQL, SQL Server, Access, Oracle, Sybase, DB2, and other database systems. This article is a lengthy one, and cover all syntax and highlights in SQL. Hope you like and share this.

PREPARATION for LIFETIME

WHAT IS SQL?

SQL is an ANSI (American National Standards Institute) standard, there are different versions of the SQL language.

SQL stands for Structured English Query Language. Because It follows the syntax structure of English. The original full name of SQL was SEQUEL, which stood for “Structured English Query Language“. Later on it had to be renamed to SQL due to trademark issues.

SQL is a standard language for accessing databases.

SQL keywords are not case sensitive.

SQL lets you access and manipulate databases.

SQL is called a non Procedural language because the programmer or the user only specifies what is needed and not tell the compiler how to do it, as done in Procedural language. Procedural capabilities are given to SQL using T-SQL or PL/SQL.

WHAT ARE THE FUNCTIONALITIES OF SQL?

SQL can execute queries against a database

SQL can retrieve data from a database.

SQL can insert records in a database.

SQL can update records in a database.

SQL can delete records from a database.

SQL can create new databases.

SQL can create new tables in a database.

SQL can create stored procedures in a database.

SQL can create views in a database.

SQL can set permissions on tables, procedures, and views.

SQL used to build Web Sites. To build a web site that shows data from a database, you will need RDMS Database program (i.e. MS Access, SQL Server, MySQL).

SQL used in a server-side scripting language, like PHP or ASP.

WHAT IS RDBMS?

RDBMS stands for Relational Database Management System.

A table is a collection of related data entries and it consists of columns and rows.

The data in RDBMS is stored in database objects called tables.

SHARE SOME POINTS IN “DATABASE TABLES”

A database can contain one or more tables with specific names (e.g. “TEACHER”, “DEPARTMENT”, etc.). Tables contain records (rows) with data.

Teacher table
Teacher table

The table above contains seven columns (Id, Description, First_name, Last_name, Email, Phone) each column have specific values in it.

Below is a selection from the “Teacher” table:

IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONE
1DBMSAlfredskusteralfredskuster@yahoo.com572222132
3DBMSHarryJonathanharryJonathan@yahoo.com258656854
5C++KelvinMitnickkelvinMitnick@yahoo.com453656854
6PYTHONMichaelcalceMichaelcalce@yahoo.com487656854
7JohnkartJohncart@yahoo.com654256854
4PYTHONEnriqueharnenriqueharn@yahoo.com452536425
2C++TomRiddleTomriddle@yahoo.com454522132
Teacher table

The table above contains six records (one for each teacher) and seven columns that contains each teacher’s data values.

To perform any action we put some queries in the Editor. There are some of the most important SQL commands are given below. Commands are like in Terminal or MS-DOS etc.

SQL COMMANDS

  • SELECT – extracts data from a database
  • UPDATE – updates data in a database
  • DELETE or TRUNCATE – deletes data from a database
  • INSERT INTO – inserts new data into a database
  • CREATE DATABASE – creates a new database
  • ALTER DATABASE – modifies a database
  • CREATE TABLE – creates a new table
  • ALTER TABLE – modifies a table
  • DROP TABLE – deletes a table
  • CREATE INDEX – creates an index (search key)
  • DROP INDEX – deletes an index

SELECT STATEMENT

The Select statement selects the recorded values(data) in the table based on Conditions(explained later).

The result is stored in a result table, called the result-set.

Select operator does not work when there are no record in the table, it returns null or no data found as output.

The given above table is fetch from the select statement.

Syntax:

There are two select statements first one is used for columns specific values, and the second one selects all attributes from the table.

As SQL is not Case sensitive, select is the same as SELECT.

SELECT DISTINCT

The DISTINCT clause counts only those columns having distinct (unique) values.

To remove duplicate values from the result-set, you can use Distinct clause.

In a table, A column may contain many duplicate values considering a large data-set; and sometimes you only want to list the different (distinct) values in the result-set. Simply use a distinct clause.

Distinct contains Null values. Given below figure clears your doubt.

Syntax :

DESCRIPTION
DBMS
PYTHON
C++
Output of distinct description

Distinct can be used as Nested Queries.

SELECT TOP COMMAND

In order to return a specific number of records, you can use the SELECT TOP clause. There are some database systems support the SELECT TOP clause.

The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact performance.

The SELECT TOP clause vary System to System.

SQL Server / MS Access Syntax

SQL SELECT TOP Equivalent in MySQL and Oracle

Oracle Syntax

IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONE
1DBMSAlfredskusteralfredskuster@yahoo.com572222132
2C++TomRiddleTomriddle@yahoo.com454522132
3DBMSHarryJonathanharryJonathan@yahoo.com258656854
Select top output

This SELECT TOP command is very useful when Maximum, Highest, and First marks values are required.

WHERE CLAUSE

The where clause is used as a condition-checker, based on that condition-specific values are fetched.

Syntax:

IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONE
7JohnkartJohncart@yahoo.com654256854
Text field example
IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONE
2C++TomRiddleTomriddle@yahoo.com454522132
Numeric field example

NUMERIC VS TEXT FIELDS

SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes.

OPERATORS IN THE WHERE CLAUSE
OPERATORDESCRIPTION
=Equal
<>Not equal. Note: In some versions of SQL this operator may be written as !=
>Greater than
<Less than
>=Greater than or equal
<=Less than or equal
BETWEENBetween an inclusive range
LIKESearch for a pattern
INTo specify multiple possible values for column
These Operators can be used in the WHERE clause.

AND & OR OPERATOR

For more than one condition based SQL queries, We can use AND & OR operators to filter records.

The And operator fetch records when both conditions are true.

Out of the many conditions if only one condition is true then the Or operator fetch records successfully.

Syntax:

IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONE
5C++KelvinMitnickkelvinMitnick@yahoo.com453656854
6PYTHONMichaelcalceMichaelcalce@yahoo.com487656854
4PYTHONEnriqueharnenriqueharn@yahoo.com452536425
2C++TomRiddleTomriddle@yahoo.com454522132
Result-set of OR operation
IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONE
6PYTHONMichaelcalceMichaelcalce@yahoo.com487656854
Result-set of AND operation

Combining And & Or :

For complex and Nested queries, Combining And & Or operators comes in the form.

ORDER BY

Sorting like Ascending or Descending order can also be done in the Data-set. To perform sorting operation, you can use ORDER BY clause.

By default Sorting order is Ascending. To sort the records in ascending or descending order, you can use the ASC |DESC keyword.

If you noticed that our Teacher table is not sorted.

Syntax:

IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONE
7JohnkartJohncart@yahoo.com654256854
4PYTHONEnriqueharnenriqueharn@yahoo.com452536425
6PYTHONMichaelcalceMichaelcalce@yahoo.com487656854
1DBMSAlfredskusteralfredskuster@yahoo.com572222132
3DBMSHarryJonathanharryJonathan@yahoo.com258656854
5C++KelvinMitnickkelvinMitnick@yahoo.com453656854
2C++TomRiddleTomriddle@yahoo.com454522132
Result-set Description as Descending order and First_name as Ascending order
IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONE
5C++KelvinMitnickkelvinMitnick@yahoo.com453656854
2C++TomRiddleTomriddle@yahoo.com454522132
1DBMSAlfredskusteralfredskuster@yahoo.com572222132
3DBMSHarryJonathanharryJonathan@yahoo.com258656854
4PYTHONEnriqueharnenriqueharn@yahoo.com452536425
6PYTHONMichaelcalceMichaelcalce@yahoo.com487656854
7JohnkartJohncart@yahoo.com654256854
By default Description and First_name sorted in Ascending order

YOU MIGHT LIKE:
C program to reverse a Number
Oracle Live SQl Editor
FAQ:

DOES SEMICOLON MATTERS IN SQL?

Putting a semicolon at the end of every query is a part of good programming practice. It depends on database systems. You check once, before run queries.

There are NOTE 2, NOTE 3, and NOTE 4 published, you definitely get help from it, so read all published notes.

1 thought on “SQL PREPARATION TIME NOTE 1

Leave a Reply

Your email address will not be published. Required fields are marked *

Close Bitnami banner
Bitnami