Software Testing & Quality Assurance Slides - Week 5
Date Published: 05/06/2019
In these slides, we will understand the basic of the database and how to write the queries to get the data from database tables. We will learn different operators with examples e.g. Where, Order By and Like that help to specify the conditions to filter the data.

  • Database basic understanding
  • Basic Database Operators:
    • Select
    • Where
    • OrderBy
    • Like

    What is Database?
    • The database is a set of data. e.g. list of students in a class
    • A database is a collection of information that is organized so that it can easily be accessed, managed, and updated.
    • Traditional databases are organized by fields, records, and tables. A field is a single piece of information; a record is one complete set of fields, and a table is a collection of records.

      Database Management System (DBMS)
      • A Database Management System [DBMS] is a software that handles the storage, retrieval, and updating of data in a computer system.
      • A DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.
      • Some popular relational database management systems are:
        • Microsoft Access
        • Microsoft SQL Server
        • MySQL
        • Oracle

        Database Management System (DBMS) Contd...
        • In a DBMS, data is organized in tables.
        • One table usually contains data about one real-world entity. Each table in a database is given a unique name. E.g. Employee table, Customer table, Sale table, etc
        • A table consists of fields. Each field in a table has a unique name. e.g. FirstName, LastName, PhoneNumber etc

          Microsoft SQL Server
          • Different database management systems look different, but generally, there are a number of common features that you'll usually see across most of them.
          • Microsoft SQL Server is one of the most widely used DBMS in the industry.
          • Today we will explore Microsoft SQL Server to get familiar with some of the key concepts of the database management systems.

            Microsoft SQL Server Management Studio

              Structured Query Language (SQL)
              • Structured Query Language (SQL) is used to perform many of the database tasks. Including creating a database, creating a table, inserting, updating and deleting data, etc.
              • In MS SQL Server, SQL instructions are written in a Query Analyzer window.
              • SQL instruction is also called query. 
              • The query is not case sensitive.
              • Example to create a new database using SQL, write the below line in Query Analyzer and then press F5 button (execute the command).

              CREATE DATABASE MyDatabase

                Select Statement
                • The select statement is used to select data from the database
                • Syntax of the select query is:
                  • Select [one or more field names] from [Table Name]
                    • Select FirstName from Employee
                  • FirstName is the field of Employee table.
                  • Select FirstName, LastName from Employee
                  • Field names are separated by a comma

                  Select Statement Contd...
                  • To select all fields of a table * can be used instead of writing names of all fields.
                    • Select * from Employee
                  • DISTINCT is used to return only distinct (different) values.
                    • Select DISTINCT * from Employee
                  • Exercise
                    • Select data from sale, customer and inventory tables

                    Where Clause
                    • The WHERE clause is used to extract only those records that fulfill a specified criteria
                    • WHERE clause syntax
                      • SELECT column_name,column_name FROM table_name WHERE column_name operator value;
                    • Example
                      • Select Id, FirstName, LastName from Employee where Id > 5

                      The AND and OR Operators
                      • The AND & OR operators are used to filter records based on more than one condition
                      • The AND operator displays a record if both the first condition AND the second condition are true.

                      SELECT * FROM EMPLOYEE

                      WHERE Id >5

                      AND FIRSTNAME = ‘John’

                        The AND and OR Operators Contd...
                        • The OR operator displays a record if either the first condition OR the second condition is true.

                        SELECT * FROM EMPLOYEE

                        WHERE Id >5

                        OR FIRSTNAME = ‘John’

                          ORDER BY Operator
                          • The ORDER BY keyword is used to sort the result-set.
                          • The ORDER BY keyword sorts the records in ascending order by default. 

                          SELECT * FROM EMPLOYEE

                          WHERE Id > 2

                          ORDER BY FIRSTNAME

                            ORDER BY Operator Contd...
                            • To sort records in descending order use DESC keyword at the end of the ORDER BY 

                            SELECT * FROM EMPLOYEE

                            WHERE Id > 2

                            ORDER BY FIRSTNAME DESC

                              ORDER BY Operator Contd...
                              • The ORDER BY keyword can also sort result-set by more than one columns

                              SELECT * FROM EMPLOYEE

                              WHERE Id > 2

                              ORDER BY FIRSTNAME, LASTNAME ASC

                                LIKE Operator
                                • The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
                                • Syntax

                                SELECT column_name(s)

                                FROM table_name

                                WHERE column_name LIKE pattern;

                                The following SQL statement selects all employees with a first name starting with the letter "s":

                                SELECT FIRSTNAME FROM EMPLOYEE WHERE FIRSTNAME like ‘s%’

                                  LIKE Operator Contd...
                                  • The "%" sign is used to define wildcards (missing letters) both before and after the pattern.
                                  • The following SQL statement selects all employees with a first name ending with the letter "s":

                                  SELECT * FROM Employee

                                  WHERE FirstName LIKE '%s';

                                  • The following SQL statement selects all Employees with a phone number containing the pattern ”703":

                                  SELECT * from EMPLOYEE WHERE PhoneNumber like ‘%703%’

                                    Keywords: sql tutorial, sql like operator, sql orderby operator, sql like operator, sql tutorial for beginners