intern_sql_introduction.md - brainchildservices/curriculum GitHub Wiki

  • SQL:

    • SQL is a standard language for accessing and manipulating databases.
    • SQL stands for Structured Query Language
    • SQL lets you access and manipulate databases

  • What canSQL do?:

    • SQL can execute queries against a database
    • SQL can retrieve data from a database
    • SQL can insert records in a database
    • SQL can update records in a database
    • SQL can delete records from a database
    • SQL can create new databases
    • SQL can create new tables in a database
    • SQL can create stored procedures in a database
    • SQL can create views in a database
    • SQL can set permissions on tables, procedures, and views

  • RDBMS

    • To build a web site that shows data from a database, you will need an RDBMS database program (i.e. MS Access, SQL Server, MySQL).
    • RDBMS stands for Relational Database Management System.
    • RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

  • TABLE:

    • The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.
    • Every table is broken up into smaller entities called fields. A field is a column in a table that is designed to maintain specific information about every record in the table.
    • A record, also called a row, is each individual entry that exists in a table. A record is a horizontal entity in a table.
    • A column is a vertical entity in a table that contains all information associated with a specific field in a table.
    • A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.

  • SQL STATEMENTS:

    • Most of the actions you need to perform on a database are done with SQL statements.

    • The following SQL statement selects all the records in the "Students" table:

                                      SELECT * FROM Students;

    • SQL keywords are NOT case sensitive: select is the same as SELECT

    • Some database systems require a semicolon at the end of each SQL statement.

    • Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

  • Some of The Most Important SQL Commands

                          SELECT - extracts data from a database
                      UPDATE - updates data in a database
                      DELETE - deletes data from a database
                      INSERT INTO - inserts new data into a database
                      CREATE DATABASE - creates a new database
                      ALTER DATABASE - modifies a database
                      CREATE TABLE - creates a new table
                      ALTER TABLE - modifies a table
                      DROP TABLE - deletes a table
                      CREATE INDEX - creates an index (search key)
                      DROP INDEX - deletes an index

  • The SQL SELECT Statement:

    • The SELECT statement is used to select data from a database.

    • The data returned is stored in a result table, called the result-set.

    • SELECT Syntax:

                        SELECT column1, column2, ...
                  FROM table_name;

    • Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

                        SELECT * FROM table_name;

  • The SQL WHERE Clause:

    • The WHERE clause is used to filter records.

    • It is used to extract only those records that fulfill a specified condition.

    • WHERE Syntax:

                         SELECT column1, column2, ...
                   FROM table_name
                   WHERE condition;

                   Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.!

    • WHERE Clause Example:

                         The following SQL statement selects all the students from the district "Ernakulam", in the "Students" table:

                   SELECT * FROM Students
                   WHERE District='Ernakulam';

  • The SQL INSERT INTO Statement

    • The INSERT INTO statement is used to insert new records in a table.

    • INSERT INTO Syntax:

      • It is possible to write the INSERT INTO statement in two ways:

      1. Specify both the column names and the values to be inserted:

                      INSERT INTO table_name (column1, column2, column3, ...)
              VALUES (value1, value2, value3, ...);

      2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

                      INSERT INTO table_name
              VALUES (value1, value2, value3, ...);

    • INSERT INTO Example:

      • The following SQL statement inserts a new record in the "Students" table:

                       INSERT INTO Students (StudentName, ContactName, Address, City, PostalCode, District)
               VALUES ('Tom', 'Tom B. Erichsen', 'Cochin 21', 'Kochi', '6821', 'Ernakulam');

      • Insert Data Only in Specified Columns:

                       INSERT INTO Students (StudentName, City, District)
               VALUES ('Tom', 'Kochi', 'Ernakulam');

  • SQL NULL Values:

    • A field with a NULL value is a field with no value.
    • If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
    • Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!
    • How to Test for NULL Values?

      • It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

      • We will have to use the IS NULL and IS NOT NULL operators instead.

      • IS NULL Syntax:

                       SELECT column_names
               FROM table_name
               WHERE column_name IS NULL;

      • IS NOT NULL Syntax:

                       SELECT column_names
               FROM table_name
               WHERE column_name IS NOT NULL;

  • The SQL UPDATE Statement:

    • The UPDATE statement is used to modify the existing records in a table.

    • UPDATE Syntax:

                         UPDATE table_name
                   SET column1 = value1, column2 = value2, ...
                   WHERE condition;

    • UPDATE Table:

      • The following SQL statement updates the first student (StudentID = 1) with a new contact person and a new city.

                       UPDATE Customers
               SET ContactName = 'Rahul', City= 'Calicut'
               WHERE StudentID = 1;

    • UPDATE Multiple Records:

      • It is the WHERE clause that determines how many records will be updated.

      • The following SQL statement will update the ContactName to "Harry" for all records where district is "Idukki":

                       UPDATE Customers
               SET ContactName='Harry'
               WHERE District='Idukki';

    • NOTE: Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

  • The SQL DELETE Statement

    • The DELETE statement is used to delete existing records in a table.

    • DELETE Syntax:

                         DELETE FROM table_name WHERE condition;

    • SQL DELETE Example:

      • The following SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers" table:

                       DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

    • Delete All Records:

      • It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

                       DELETE FROM table_name;

      • The following SQL statement deletes all rows in the "Customers" table, without deleting the table:

                       DELETE FROM Customers;

  • The SQL CREATE TABLE Statement

    • The CREATE TABLE statement is used to create a new table in a database.

    • Syntax:

                         CREATE TABLE table_name (
                       column1 datatype,
                       column2 datatype,
                       column3 datatype,
                      ....
                   );

                   * The column parameters specify the names of the columns of the table.
                   * The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).

    • SQL CREATE TABLE Example:

      • The following example creates a table called "Persons" that contains five columns: PersonID, FirstName, LastName, Address, and City:

                       CREATE TABLE Persons (
                   PersonID int,
                   FirstName varchar(255),
                   LastName varchar(255),
                   Address varchar(255),
                   City varchar(255)
               );

      • The PersonID column is of type int and will hold an integer.

      • The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.

  • REF Link: https://www.w3schools.com/sql/default.asp

⚠️ **GitHub.com Fallback** ⚠️