SQL & PL/SQL BOOK FOR BEGINNERS

 

A simplified quickstart beginner's guide to learn the fundamentals of SQL & PL/SQL

THE BEST SQL & PL/SQL QUICKSTART BOOK FOR BEGINNERS IN 2021

If you have planned to build a career in the data-driven world, then this book is your perfect guide to learn SQL quickly and step into the exciting world of big data and computer programming.

Why SQL?

SQL is the mainstream language that is used to access databases to handle massive data. It is the platform that forms the backbone of modern data management. Here are the best reasons to invest time to learn SQL:

1. High Paying Jobs
2. SQL and data analysis are skills sought after by many employers
3. Quick Access To Data
4. Data Manipulation
5. Manage Huge Amounts Of Data
6. Combine Data From Multiple Sources
7. Perform Data Mining
8. A universal language that is not going anywhere

What's inside for you

• The essential concepts of Relational Database Management System (RDBMS)
• Visually understand the use of SQL to store and retrieve data from database
• Lots of hands-on exercises along with illustrations
• Free access to a database to polish your skills
• Each chapter ends with a "Test Your Skill" section comprising quizzes
• PL/SQL, a programming language that uses detailed sequential instructions to process data

Whether you are a beginner with no prior experience or a professional who needs a skill to get business insights from massive data, this is the book that helps you master SQL quickly.

Buy From Amazon

The UPPER Function in SQL

The UPPER function is contrary to the LOWER function and returns all characters in uppercase letters as shown in the following statement where each employee’s first name is displayed in uppercase.

Microsoft Access uses UCASE() function.

    Syntax :
UPPER (character)

    SQL Statement :

SELECT    UPPER(first_name)

FROM       employees;

    Output :

Date and Time Functions in SQL

Every DBMS allows you to store date and time values in tables using specific date data types and in specific formats. Each implementation follows its own storage format to save the date and time, for example, Oracle’s default display and input format for any date is DD-MON-YY. Unfortunately, this default storage varies among different implementations and is, therefore, least portable.

Similar to the character functions, date and time functions are also used to manipulate the representation of data. These function are used not only to present date and time data in some desirable format, but are also helpful in comparing date values, and computing intervals between dates.

To start with, here is an example in Oracle which fetches current date from the system, followed by a list of functions that perform the same task for other platforms.

    SQL Statement :

SELECT    SYSDATE

FROM        DUAL;

    Output :

The following table lists respective functions as employed by various DBMSs to get current system date:

DBMS	FUNCTION	SQL STATEMENT	OUTPUT (Format)
MySQL/MariaDB	CURDATE()	SELECT CURDATE();	2014-11-19 (YYYY-MM-DD)
PostgreSQL	CURRENT_DATE	SELECT CURRENT_DATE;	2014-11-19 (YYYY-MM-DD)
Microsoft Access	NOW()	SELECT Now() FROM Products;	11/19/2014 2:28:17 AM
Microsoft SQL Server	GETDATE()	SELECT GETDATE();	2014-11-19 13:10:02.047
SQLite	DATE()	SELECT DATE('now');	2014-11-19 (YYYY-MM-DD)
DB2	CURRENT DATE	SELECT CURRENT DATE FROM sysibm.sysdummy1;	2014-11-19 (YYYY-MM-DD)

The SUBSTR Function in SQL

The SUBSTR function returns specified characters from character value, starting from P, L characters long.  The following statement fetches records of employees who have ‘lex’ (three characters) in their names starting from second position.

NOTE

In Microsoft Access use MID() function. In Microsoft SQL Server, MySQL, and MariaDB you have to use SUBSTRING().

    Syntax :

SUBSTR (character, P,L)

    SQL Statement :

SELECT    first_name, last_name, salary

FROM      employees

WHERE    substr(first_name,2,3)='lex';   

    Output :

The NVL Function in SQL

When you query a table, the null values stored in a column (e.g commission_pct) are shown as (null). Using the NVL function, you can replace these nulls with some meaningful values. According to the following syntax, if expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.  In the following example, you get a list of employees along with their commissions, replacing null values with zero if the employees are not entitled to get commissions.

NOTE

In the presented example we used a number column and replaced all null values with zero. Null values in a character column must be replaced as shown in the following example, where a character value (‘None’) is used to replace all null values in the state province column.

SELECT city,NVL(state_province, 'None') Province FROM locations;

    Syntax :

NVL (expr1, expr2)

    SQL Statement :

SELECT    last_name, NVL(commission_pct, 0) commission

FROM      employees

WHERE    last_name LIKE 'B%'

ORDER    BY last_name;

    Output  :

The LOWER Function in SQL

The LOWER function returns character, with all letters lowercase. The example presented below transforms all data in first name and job id columns to lower case.

  NOTE   Use LCASE() function for the same purpose in Microsoft Access.

NOTE

As mentioned earlier, single row functions can also be used in the WHERE clause. Let’s see an example. Running the first statement below will not yield any result. Change it so that it matches the second statement. Now you’ll get some rows. The LOWER() function in the second statement fetched the result, because all the job ids are stored in uppercase in the employees table, and you forced the query (by using the LOWER function) to first convert all the column values to lower case, match each value with the provided lower case string (pu_clerk), and then return the result.

Select first_name, last_name, job_id from employees where job_id='pu_clerk';

Select first_name, last_name, job_id from employees where lower(job_id)='pu_clerk';

    Syntax :

LOWER (character)

    SQL Statement :

SELECT    LOWER(first_name||' '||last_name) Name, LOWER(job_id) Job

FROM       employees;

    Output  :

The LENGTH Function in SQL

The LENGTH function returns the length of character. Note that it is a character function that returns the answer in numbers, as shown in the following example where it is used to count number of character in the first names of employees.

NOTE

In Microsoft SQL Server and Microsoft Access use DATALENGTH() and LEN() functions, respectively.

    Syntax :

LENGTH (character)

    SQL Statement :

SELECT    first_name, LENGTH(first_name) Length

FROM       employees;

    Output :