SQL Part 3: Create new table, Alias, Merge columns

Query to Create EmployeeDetail table:

CREATE TABLE EmployeeDetail

(

EmpId integer PRIMARY KEY, 

FirstName varchar(255),

LastName varchar(255),

Salary integer,

JoiningDate date, 

Department varchar(255),

Gender varchar(255) 

);

INSERT INTO EmployeeDetail VALUES(1,'Tom','Garcia',60000.00,'2008-11-10 13:23:44','IT','Male');

INSERT INTO EmployeeDetail VALUES(2,'Lucy','Martin',70000.00,'2009-11-09 13:23:44','HR','FeMale');

INSERT INTO EmployeeDetail VALUES(3,'Frank','Clark',75000.00,'2012-01-24 13:23:44','Admin','Male');

INSERT INTO EmployeeDetail VALUES(4,'Jane','Joseph',78000.00,'2017-01-28 13:23:44','ICT','FeMale');

INSERT INTO EmployeeDetail VALUES(5,'Robert','Hansen',100000.00,'2012-01-29 13:23:44','HR','Male');

Query to Create ProjectDetail table:

CREATE TABLE ProjectDetail

(

ProjectDetailId integer PRIMARY KEY, 

EmpId integer REFERENCES EmployeeDetail(EmpId), 

ProjectName varchar(255) 

);

INSERT INTO ProjectDetail VALUES(1,1,'ABC');

INSERT INTO ProjectDetail VALUES(2,1,'DDC');

INSERT INTO ProjectDetail VALUES(3,1,'GHI');

INSERT INTO ProjectDetail VALUES(4,2,'HGT');

INSERT INTO ProjectDetail VALUES(5,3,'ABC');

INSERT INTO ProjectDetail VALUES(6,4,'GHI');

INSERT INTO ProjectDetail VALUES(7,3,'XYZ');

INSERT INTO ProjectDetail VALUES(8,5,'DDC');

Alias: Aliases are often used to make column names more readable

Basic SQL Queries for Practice:

1. Write query to get all employee detail from "EmployeeDetail" table:

ANS: SELECT * FROM EmployeeDetail

2. Write query to get only "FirstName" column from " EmployeeDetail " table

ANS: SELECT FirstName FROM EmployeeDetail

3. Write query to get FirstName in upper case as "First Name".
ANS: SELECT UPPER(FirstName) AS [First Name] FROM EmployeeDetail

4. Write query to get FirstName in lower case as "First Name".
ANS: SELECT LOWER(FirstName) AS [First Name] FROM EmployeeDetail

5. Write query for combine FirstName and LastName and display it as "Name" (also include white space between first name & last name).
ANS: 
1st Way - SELECT CONCAT(FirstName+' ',LastName) AS Name FROM EmployeeDetail
2nd Way - SELECT FirstName+' '+LastName AS Name FROM EmployeeDetail

Below are some very good websites to Practice SQL Queries online:

SQL Part 2: Data types, Operators and Constraints

 SQL Datatypes:

Exact Numeric SQL Data Types:

bigint = Range from -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

int = Range from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

smallint = Range from -2^15 (-32,768) to 2^15-1 (32,767)

tinyint = Range from 0 to 255

bit = 0 and 1

decimal = Range from –10^38 +1 to 10^38 -1

numeric = Range from -10^38 +1 to 10^38 -1

money = Range from -922,337,203,685,477.5808 to +922,337,203,685,477.5807

small money = Range from -214,748.3648 to +214,748.3647

Approximate Numeric SQL Data Types:

float = Range from -1.79E + 308 to 1.79E + 308

real = Range from -3.40E + 38 to 3.40E + 38

Date and Time SQL Data Types:

datetime = From Jan 1, 1753 to Dec 31, 9999

smalldatetime = From Jan 1, 1900 to Jun 6, 2079

date = To store a date like March 27, 1986

time = To store a time of day like 12:00 A.M.

Character Strings SQL Data Types:

char = CHAR is fixed lengt. Maximum length of 8,000 characters

varchar = VARCHAR is variable length. Maximum of 8,000 characters

varchar(max) = Maximum length of 231 characters

char vs varchar

CHAR takes up 1 byte per character. So, a CHAR(100) field (or variable) takes up 100 bytes on disk, regardless of the string it holds. VARCHAR is a variable length string data type, so it holds only the characters you assign to it. VARCHAR takes up 1 byte per character, + 2 bytes to hold length information.

Unicode Character Strings SQL Data Types:

nchar = Maximum length of 4,000 characters

nvarchar = Maximum length of 4,000 characters

nvarchar(max) = Maximum length of 231 characters

ntext = Maximum length of 1,073,741,823 characters

Binary SQL Data Types:

binary = Maximum length of 8,000 bytes

varbinary  = Maximum length of 8,000 bytes

varbinary(max) = Maximum length of 231 bytes

image = Maximum length of 2,147,483,647 bytes

SQL Operators:

There are are three types of Operators.

  • Arithmetic Operators:
  • Comparison Operators
  • Logical Operators

Arithmetic operators

Operator Description

+ Add values of operands

Subtract values of operands

* Multiply operand’s values

/ Divide values of operands

% Modulus operation on operands

Comparison operators

In the table below, if condition gets satisfied then “True” Boolean value is returned.

Operator Description

= Determine if the values of operands are equal.

!= Check if the values of operands are not equal.

> Determine if the left operand is more than the right operand.

< Check if the right operand is more than the left operand.

>= Determine if the left operand is more than or equal to the right operand.

<= Check if the right operand is more than or equal to the left operand.

!> Determine if the right operand is not more than the left operand.

!< Check if the left operand is not more than the right operand.

Logical Operators

Operator Description

OR   - Returns true if either operand is true. Else it returns false if both the operands are false.

AND - Returns true if both operands are true. Else it returns false if either or both the operands are false 

NOT - Returns true if condition is false and returns false if the condition is true.

SQL Constraints:

  • Constraints are the rules enforced on data columns on table. 
  • These are used to limit the type of data that can go into a table. 
  • This ensures the accuracy and reliability of the data in the database. 
  • Constraints could be column level or table level. 
  • Column level constraints are applied only to one column, 
  • whereas table level constraints are applied to the whole table. 

Following are commonly used constraints available in SQL: 

1. NOT NULL Constraint: Ensures that a column cannot have NULL value. 

Example:

CREATE TABLE recipes (

  recipe_id INT NOT NULL,

  recipe_name VARCHAR(30) NOT NULL,

  PRIMARY KEY (recipe_id),

  UNIQUE (recipe_name)

);

2. DEFAULT Constraint: Provides a default value for a column when none is specified. 

3. UNIQUE Constraint: Ensures that all values in a column are different. 

4. PRIMARY Key: Uniquely identified each rows/records in a database table. 

UNIQUE KEY vs PRIMARY KEY

  • The UNIQUE constraint ensures that all values in a column are different.
  • Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
  • A PRIMARY KEY constraint automatically has a UNIQUE constraint.
  • However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
  • The primary key column cannot have null values while the Unique Key column can have one null value.

5. FOREIGN Key: Uniquely identified a rows/records in any another database table. 

  • A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
  • Foreign key is used to maintain relationship between two tables.
  • Foreign Key in a table doesn't have to be unique in said table. BUT, as it is the Primary Key of another table, it must be unique in this table.
  • Foreign Key can be NULL or duplicate
Example:

Table1 (Parent Table):

CREATE TABLE EmployeeDetail(EmpId integer PRIMARY KEY, FirstName varchar(255),LastName varchar(255),Salary integer,JoiningDate date, Department varchar(255), Gender varchar(255) );

Table 2 (Child Table):

CREATE TABLE ProjectDetail(ProjectDetailId integer PRIMARY KEY, EmpId integer REFERENCES EmployeeDetail(EmpId), ProjectName varchar(255) );

6. CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions. 

SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Syntax: ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition);

7. INDEX: Use to create and retrieve data from the database very quickly.

A SQL index is used to retrieve data from a database very fast. Indexing a table or view is, without a doubt, one of the best ways to improve the performance of queries and applications. 

Syntax: CREATE INDEX <indexName>

ON <TableName>(LastName);

SQL Part 1: DataBase Concept

 In This section we are going to cover below topics:

  • SQL For Testers
  • What is Database?
  • What is DBMS?
  • What is RDBMS?
  • What is SQL?
  • SQL Command Types
  • Most Important SQL Commands

Why SQL needed for Testers
  • The ability to recognize the different types of databases, The ability to connect to the database using different SQL connection clients, Understanding of the relationship between database tables, keys, and indices. Ability to write a simple select or SQL statement along with more complex join queries.
  • As a software tester, you are required to perform database testing that requires the knowledge of different SQL and database concepts. In addition, you are required to write SQL queries to retrieve, update and insert data in the databases. 
  • We will start with the database fundamentals. At the same time, we will move to the SQL concepts. Finally, we’ll check some of the widely used SQL commands
What is a Database?
  • A database is an organized and systematic collection of data that is stored and accessed in/from a computer system. A database is usually controlled by a database management system (DBMS).
  • For example, a company database may include tables for products, employees, and financial records. Each of these tables would have different fields that are relevant to the information stored in the table.
What is DBMS?
  • DBMS (Database Management System) is a software system that is designed to maintain and access the database. It allows the user to access, create, delete, and update data in a database.
  • DBMS defines the rules for manipulation and validation of this data. We use a database when there is a huge amount of data, the security of the data is important, or when multiple users have to have access to the data concurrently.
  • Some DBMS examples include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro
What is RDBMS?
RDBMS (Relational Database Management System) is an advanced version of the basic DBMS.
A relational database is a database that allows the user to store related data in the form of multiple tables, which are linked by establishing a relationship between the different tables, hence providing an efficient way to access the database.
  • Top Relational Databases Software
  • MS SQL
  • Oracle Database
  • MySQL
  • Db2
  • PostgreSQL
  • A RDBMS database uses tables for storing the data. A table is nothing but a collection of data that is related to one another.
  • Rows
  • A Row represents a collection of fields that end up making a record in the database.
  • Column
  • In a database, a column represents those values that are of the same type. A column is also called an attribute.
What is SQL?
  • SQL stands for Structured Query Language. It is a programming language that is used to request information from a database. SQL can be used to manage and share data in a relational database management system. Moreover, users can perform actions like insertion, deletion, selection, etc on the database.
  • SQL keywords are not case sensitive.
SQL commands are segregated into following types:
  • DDL – Data Definition Language – 
SQL commands come under DDL are as follows: CREATE, ALTER, DROP, TRUNCATE
  • DML – Data Manipulation Language
SQL Commands come under DML are as follows: INSERT, UPDATE, DELETE
  • DQL – Data Query Language
SQL Commands come under DQL are as follows: SELECT
  • DCL – Data Control Language
SQL Commands come under DCL are as follows: GRANT, REVOKE, DENY
  • TCL – Transaction Control Language
SQL Commands come under TCL are as follows: COMMIT, ROLEBACK, SAVE

Some frequently used keywords/commands:
  • SELECT – It extracts data from a DataBase
  • INSERT INTO – It inserts new data into a DataBase
  • CREATE – It creates a new DataBase/Table/Index
  • UPDATE – It updates data in a DataBase
  • ALTER – It modifies a DataBase/Table
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Syntax: ALTER TABLE table_name
ADD column_name datatype;
  • TRUNCATE -  Truncate can be used to delete the entire data of the table. Table structure remain same.
Syntax: TRUNCATE TABLE table_name
  • DELETE – Delete statement can be used for deleting the specific data.
Syntax/Example:
Delete from  <table>;
Delete from  <table> where ID=3;
  • DROP – It deletes a table/Index.
Syntax: DROP TABLE table_name

Please refer below YouTube videos in detail: