Software Testing & Quality Assurance Slides - Week 8
Date Published: 05/12/2019
In these slides, we will learn about SQL datatype e.g. decimal, varchar, etc. Later, we will see how to create the tables through SQL query, create Views and how they different SQL functions work to filter the data from the database.

Agenda
  • Basic Database Operators:
    • SQL Data Types 
    • Create Table
    • SQL Views and Functions

Data Types
  • Data Type means a particular kind of data item defined by the values it can take
  • Operations that can be performed on a data item are also defined by its data type. Some SQL data types are:
  • VARCHAR(size): Holds a variable length string (can contain letters, numbers, and special characters). Can hold up to 255 characters.
  • INT: Stores integer values. Range from 2147483648 to 2147483647.
  • DECIMAL(size,d): Stores decimal values. 
  • DATETIME(): A date and time combination. Format: YYYY-MM-DD HH:MI:SS
  • For more details about data type please visit below link
  • http://www.w3schools.com/sql/sql_datatypes.asp

      SQL Create Table
      • The CREATE TABLE statement is used to create a table in a database.
      • Tables are organized into rows and columns, and each table must have a name.

      CREATE TABLE table_name

      (

      column_name1 data_type(size),

      column_name2 data_type(size),

      column_name3 data_type(size),

      )

          SQL Create Table Contd...
          • The column_name parameters specify the names of the columns of the table.
          • The data_type parameter specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.).
          • The size parameter specifies the maximum length of the column of the table.

          CREATE TABLE Persons

          (

          PersonID int,

          LastName varchar(255),

          FirstName varchar(255),

          Address varchar(255),

          City varchar(255)

          );

          SQL Views
          • In SQL, a view is a virtual table based on the result-set of an SQL statement.
          • A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
          • You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

          CREATE VIEW view_name AS

          SELECT column_name(s) FROM table_name WHERE condition

              SQL Functions
              • SQL has many built-in functions for performing calculations on data.
              • AVG() -      Returns the average value
              • COUNT() - Returns the number of rows
              • FIRST() -  Returns the first value
              • LAST() -   Returns the last value
              • MAX() -   Returns the largest value
              • MIN() -   Returns the smallest value
              • SUM() -  Returns the sum

                  SQL FUnction Contd...

                  UCASE() -     Converts a field to upper case

                  LCASE() -     Converts a field to lower case

                  MID() -        Extract characters from a text field

                  LEN() -        Returns the length of a text field

                  ROUND() -  Rounds a numeric field to the number of decimals specified

                  NOW() -     Returns the current system date and time




                              Keywords: sql table, create sql table, sql views tutorial, sql functions tutorial