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
7-JohnkartJohncart@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: 

select column_name1, column_name2,..,column_nameN from table_name; 
select * from table_name ;
Example: 
select Description from teacher;
select * from teacher;

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 :

select distinct column_name1, column_name2 from table_name;
select distinct column_name from table_name;
Example: 
select distinct description from teacher;
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 

 SELECT TOP number|percent column_name1, column_name2,..,column_nameN FROM table_name;

SQL SELECT TOP Equivalent in MySQL and Oracle

SELECT column_name1, column_name2,..,column_nameN FROM table_name LIMIT number;
Example:
SELECT * FROM Persons LIMIT 5;

Oracle Syntax

SELECT column_name1, column_name2,..,column_nameN  FROM table_name WHERE ROWNUM <= number;
Example: 
SELECT * FROM teacher WHERE Id <=3;
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: 

Select column_name,..,column_nameN FROM table_name WHERE column_name operator value;
Example:
// TEXT FIELD EXAMPLE
select * from teacher where phone='654256854' ;
// NUMERIC FIELD EXAMPLE
select * from teacher where ID=2 ;
IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONE
7-JohnkartJohncart@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: 

select * from table_name where Condition1 and Condition2 and ... and conditionN;
select * from table_name where Condition1 or Condition2 or ... or conditionN;
Example:
select * from teacher where Description='PYTHON' or Description='C++';
select * from teacher where Description='PYTHON' and id=6;
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: 

SELECT column_name1, ..., column_nameN FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;
Example: 
SELECT * FROM teacher ORDER BY Description DESC,First_name ASC;
SELECT * FROM teacher ORDER BY Description,First_name;
IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONE
7-JohnkartJohncart@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
7-JohnkartJohncart@yahoo.com654256854
By default Description and First_name sorted in Ascending order
YOU MIGHT LIKE:
https://www.youtube.com/watch?v=r-ejnpRJb0A
C program to reverse a Number
https://www.shoutcoders.com/oracle-live-sql-editor-and-sql-quick-guide/
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.

Comments

Post a Comment

Popular posts from this blog

Oracle Live SQL

Image
  In this article, you are going to learn how to use Oracle Live SQL and how to work with Oracle Database without installing the database. Get instant access to the Oracle Database and learn from a collection of community scripts and tutorials. Write your own SQL scripts and easily share them with others. All you need to get started is your Oracle.com account. Following steps helps you to build your knowledge in SQL. Step 1: Start with a basic structure as given in the following below diagram. Fig: Table Step 2: Learn some basic terminologies i.e. Field, Row, And Column (s). So, it helps you to better understanding. Step 3: Break it into some simple steps, i.e. Create a Table, Make some conditions on attributes (integrity constraints), Inserting values, and Make sure all values are inputted in their proper place. Step 4: There are some queries and you should answer those. Step 5: Commit; After Creating and inserting values because sometimes data is not save
Read more

PL/SQL XAMPP EXAMPLE SET 2

  In this , we are going to learn how to use SQL in XAMPP. Here mainly cover CREATE, FOREIGN KEY ADD, ALTER, INSERT, SELECT, AGE RETRIEVE, TIMESTAMPDIFF, DISTINCT, LIKE, CURDATE, COUNT, SUM, MAX, AND, OR, IN, NOT IN, TRUNC, RENAME OPERATION ,UNION, BETWEEN, NESTED QUERIES. All types are discussed in the given below links, you might like, Preparation Related Notes: https://www.shoutcoders.com/sql-preparation-time-note-4/ , https://www.shoutcoders.com/sql-preparation-time-note-3/ , https://www.shoutcoders.com/sql-preparation-time-note-2/ , https://www.shoutcoders.com/sql-preparation-time-note-1/ PL/SQL XAMPP EXAMPLE SET 2: Create the following table (i) Studies ( P_name, S_place, Course, Cost_no ) create table studies ( P_name varchar(20) primary key, Study_place varchar(20), Course varchar(20), Cost_no int(20)); OUTPUT: Table Created (ii) Programmer ( P_name, DOB, DOJ, Gender, Prof1, Prof2, Sal ). Make P_name as foreign key from Studies relation. create
Read more

PL/SQL XAMPP EXAMPLE SET 1

Image
  In this PL/SQL XAMPP EXAMPLE SET 1, You can to learn how to use SQL commands in XAMPP. Here cover DML, Data Insertion, Data Updation, Data Deletion, Data Selection, CREATE, DESCRIBE SCHEMA, INSERT, ADD COLUMN, REMOVE COLUMN, UPDATE, ALTER, PRIMARY KEY, MODIFY, DROP COLUMN, DATATYPE CHANGE, RENAME FIELD, DISTINCT, DELETE RECORD, CLEAR ALL DATA, REMOVE TABLE. There are some preparation notes, links are given below, you might like, Preparation Notes: https://www.shoutcoders.com/sql-preparation-time-note-4/ , https://www.shoutcoders.com/sql-preparation-time-note-3/ , https://www.shoutcoders.com/sql-preparation-time-note-2/ , https://www.shoutcoders.com/sql-preparation-time-note-1/ Oracle Database PL/SQL XAMPP EXAMPLE SET 1 : 1. Create the following table Student {roll, name, address, ph_no, DOB, MA, HS, DOA, Stream, C_num} create table student (Roll int(4), Name varchar(10), Address varchar(20), Ph_num int(10), DOB date, MA int(4), HS int(4), DOA date, Stream var
Read more