First steps in SQL

  Creating DB :       Creating a Database - Part I - solution

                           CREATE DATABASE IF NOT EXISTS Sales;

                           CREATE SCHEMA IF NOT EXISTS Sales;

To select DB : Run - USE database_name; 

Data type Limit :

            STRING:

                    CHAR  : 255 (Maximum size) Bytes. 

                    VARCHAR : 65,535 (Maximum size) Bytes.

         INTEGER:  

                            Whole number with no decimal point (like : 2,100,-100)

       
    Fixed and floating-point data types : 

            precision :  Refers to the number of digits in a number.

             scale : Refers to the number of digits to the right of the decimal                           point in a  number.

          Decimal has a synonymous data type.It is called Numeric.

         

                                    DECIMAL=NUMERIC 

 

 

 

 Creating a Table : 

         

            

 Example :

                    CREATE TABLE sales(
                    purchase_number INT NOT NULL PRIMARY KEY                                     AUTO_INCREMENT,
                    date_of_purchase DATE NOT NULL,
                    customer_id INT,
                    item_code VARCHAR(10) NOT NULL
                                                ); 

 

 

Creating a table - exercise

Create the “customers” table in the “sales” database. Let it contain the following 5 columns: customer_id, first_name, last_name, email_address, and number_of_complaints. Let the data types of customer_id and number_of_complaints be integer, while the data types of all other columns be VARCHAR of 255.

 

Creating a table - solution

CREATE TABLE customers                                                 

(

    customer_id INT,

    first_name varchar(255),

    last_name varchar(255),

    email_address varchar(255),

    number_of_complaints int

);

                        

                         Using databases and tables 

    we can use the database & tables in two ways :

       1:  USE database_name;

            SELECT * FROM related_table_name;

     

        2:  SELECT  * FROM DATABASE_NAME.related_table_name;

 

Using databases and tables - exercise

Use the same SELECT statement structure as the one shown in the lecture to select all records from the “sales” table. Do it twice – once specifying the name of the database explicitly in the SELECT statement, and once, without that specification.

 

Using databases and tables - solution

SELECT * FROM sales;

SELECT * FROM sales.sales;   

                   

           Additional notes on using tables - exercise

In SQL, dropping a table is like dropping a database. If you have not removed the ‘sales’ table during the last lecture, please use the following code to drop the ‘sales’ table from the ‘sales’ database:

DROP TABLE sales;