Learning SQL

0
1117

Hello Friends!! In this tutorial we will learn to write SQL statements to interact with data in the database.

  • SQL stands for structured query language .
  • It gives you a language for interacting with data in a database.
  • There are four general things you do to tables:
    1. Create : Putting data into tables.
    2. Read : Query data out of table.
    3. Update : Change data already in table.
    4. Delete : Remove data from table.

  • So to get familiar with SQLite follow the Exercises.
  • Exercise 1:The Setup
    1. Download sqlite3.exe file for your machine (32-bit/64-bit)as required
    2. First of all create a new folder in your C drive for eg :> sqlite2 and copy the given sqlite3.exe file in it.
    3. Now run your command prompt (cmd)..
    4. Go in your C drive and enter into your folder i.e “sqlite2” and type sqlite3 and your database name with extension “.db”, here my database name is data.
    5. 1

    6. Now just give a test try :
    7. Now simply create a table in it for example:
    8. [code language=”sql”]
      create table test (id integer);
      .quit
      [/code]

      2

    9. Now check in your sqlite2 folder if your database file is created .
    10. If it is created than you are all set.

  • Exercise 2 : Creating Tables
    1. The first sql statement to learn is CREATE .
    2. Now write the following statement in the command prompt to create table person.
      [code language=”sql”]CREATE TABLE person (
      id INTEGER PRIMARY KEY,
      first_name TEXT ,
      last_name TEXT,
      age INTEGER);
      [/code]
    3. 3

    4. Now in this , id column which will be used to exactly identify each row.
      The format of a column is NAME TYPE, and in this case we are specifying that we want an INTEGER that is also a PRIMARY KEY. Doing this tells SQLite3 to treat this column special.
    5. first_name and last_name column are both of type TEXT.
    6. An age column that is just a plain INTEGER
    7. End the list of columns with a closing parenthesis and then a semi-colon ‘;’ character.
    8. SQL is mostly a case-insensitive language.

  • Exercise 3 : Creating A Multi-Table Database
    1. Now make three tables in which we want to store data into:

      [code language=”sql”]CREATE TABLE person (
      id INTEGER PRIMARY KEY,
      first_name TEXT,
      last_name TEXT,
      age INTEGER);
      [/code]

      [code language=”sql”]CREATE TABLE pet (
      id INTEGER PRIMARY KEY,
      name TEXT,
      breed TEXT,
      age INTEGER,
      dead INTEGER
      );
      [/code]

      [code language=”sql”]CREATE TABLE person_pet (
      person_id INTEGER,
      pet_id INTEGER
      );
      [/code]
    2. 4

    3. In this file you are making tables for two types of data, and then “linking” them together with a third table.
    4. Tables that have data are “tables”, and tables that link tables together are called “relations”.
    5. Now look at person_pet you’ll see that we have made two columns: person_id and pet_id. To link two tables together is simply insert a row into person_pet that had the values of the two row’s id columns we wanted to connect.
    6. For example, if person contained a row with id=20 and pet had a row with id=98, then to say that person owned that pet, we would insert person_id=20, pet_id=98 into the person_pet relation (table).
    7. Now enter “.schema“command to view the schema of the tables made by you .
    8. The “schema” should match what you typed in.
    9. 5

  • Exercise 4 : Inserting Data
    1. Now we have three table with us so now we will insert some data into it using the INSERT command :

      [code language=”sql”]INSERT INTO person (id, first_name, last_name, age)
      VALUES (0, "Suresh", "Sharma",30);
      [/code]

      [code language=”sql”]INSERT INTO pet (id, name, breed, age, dead)
      VALUES (0, "Tyson", "Lab", 10, 0);
      [/code]

      [code language=”sql”]INSERT INTO pet VALUES (1, “Fluffy", "Pomerian", 1, 1);[/code]
    2. 6

  • Exercise 5 : Inserting Refrential Data
    1. In the last exercise you filled some tables with people and pets. The only thing that was missing is who owns what pets, and that data goes into the person_pet table like this:

      [code language=”sql”]INSERT INTO person_pet (person_id, pet_id) VALUES (0, 0);[/code]

      [code language=”sql”]INSERT INTO person_pet VALUES (0, 1);[/code]
    2. 7

    3. How this works is we are using the id values from the person row we want (in this case, 0) and the id from the pet rows we want (again, 0 for the Lab and 1 for the Dead Pomerian). We then insert one row into person_pet relation table for each “connection” between a person and a pet.

  • Exercise 6 : Selecting Data
    1. You can create tables and you can create rows in those tables. Now let’s see how to “Read” or in the case of SQL, SELECT :

      [code language=”sql”] SELECT * FROM person;[/code]

      This says “select all columns from person and return all rows.” The format for SELECT is SELECT what FROM tables(s) WHERE (tests) and the WHERE clause is optional. The ‘*’ (asterisk) character is what says you want all columns.

      [code language=”sql”] SELECT name, age FROM pet;[/code]

      In this one we are only asking for two columns name and age from the pet table. It will return all rows.

      [code language=”sql”] SELECT name, age FROM pet WHERE dead = 0;[/code]

      Now we are looking for the same columns from the pet table but we are asking for only the rows where dead = 0. This gives us all the pets that are alive.

      [code language=”sql”] SELECT * FROM person WHERE
      first_name != "Suresh";[/code]

      Finally we are selecting all columns from person just like in the first line, but now we are saying only if they do not equal “Suresh”. That WHERE clause is what determines which rows to return or not.

    2. 8

  • Exercise 7 : Select Across Many Tables
    1. Imagine you want to know what pets Suresh owns. You need to write a SELECT that looks in person and then “somehow” finds Suresh pets.
    2. To do that you have to query the person_pet table to get the id columns you need.
    3. Let’s do it:

      [code language=”sql”] SELECT pet.id, pet.name, pet.age, pet.dead
      FROM pet, person_pet, person
      WHERE
      pet.id = person_pet.pet_id AND
      person_pet.person_id = person.id AND
      person.first_name = "Suresh";
      [/code]
    4. we only want some columns from pet so we specify them in the SELECT. Instead, we want to be explicit and say what column from each table we want, and we do that by using table.column as in pet.name.
    5. To connect pet to person we need to go through the person_pet relation table. In SQL that means we need to list all three tables after the FROM.
    6. Start the WHERE clause.
    7. First we connect pet to person_pet by the related id columns pet.id and person_pet.id.
    8. AND we need to connect person to person_pet in the same way.
    9. Now the database can search for only the rows where the id columns all match up, and those are the ones that are connected.
    10. AND we finally ask for only the pets that Suresh own by adding a person.first_name test for the first name.
    11. 9

  • Exercise 8 : Deleting Data
    1. We had “SELECT * FROM” for SELECT, and “INSERT INTO” for INSERT, Similarly we write the DELETE format :

      • /* make sure there’s dead pets */
      [code language=”sql”]SELECT name, age FROM pet WHERE dead = 1;[/code]

      • /* owh!! poor Pomerian*/
      [code language=”sql”]DELETE FROM pet WHERE dead = 1;[/code]

      • /* make sure the Pomerian is gone */
      [code language=”sql”]SELECT * FROM pet;[/code]

      • /* let’s bring resurrect the pomerian*/
      [code language=”sql”]INSERT INTO pet VALUES (1, "Fluffy", "Pomerian", 1, 0);[/code]

      • /* the Pomerian LIVES! */
      [code language=”sql”]SELECT * FROM pet;[/code]
    2. The DELETE command is nearly the same format as other commands.
    3. When you enter the commands as stated above you will get the following results :
    4. 10

  • Exercise 9 : Deleting Using Other Tables
    1. “DELETE is like SELECT but it removes rows from the table.” The limitation is you can only delete from one table at a time.
    2. That means to delete all of the pets you need to do some additional queries and then delete based on those.
    3. So let’s use the following delete statement :

      [code language=”sql”] DELETE FROM pet WHERE id IN (
      SELECT pet.id
      FROM pet, person_pet, person
      WHERE
      person.id = person_pet.person_id AND
      pet.id = person_pet.pet_id AND
      person.first_name = "Suresh"
      );
      [/code]

      [code language=”sql”] SELECT * FROM pet;[/code]

      [code language=”sql”] SELECT * FROM person_pet;[/code]

      [code language=”sql”] DELETE FROM person_pet
      WHERE pet_id NOT IN (
      SELECT id FROM pet
      );
      [/code]


      [code language=”sql”] SELECT * FROM person_pet;[/code]
    4. The DELETE command starts off normally, but then the WHERE clause uses IN to match id columns in pet to the table that’s returned in the subquery.
    5. The subquery (also called a subselect) is then a normal SELECT and it should look really similar to the ones we had done before when trying to find pets owned by people.
    6. On lines 13-16 we then use a subquery to clear out the person_pet table of any pets that don’t exist anymore by using NOT IN rather than IN.
    7. How SQL does this is with the following process:

      Runs the subquery in the parenthesis at the end and build a table with all the columns just like a normal SELECT.

      Treats this table as a kind of temporary table to match pet.id columns against.

      Goes through the pet table and deletes any row that has an id IN this temporary table.

    8. You should see that after you DELETE the SELECT returns nothing.
    9. 11

  • Exercise 10 : Updating Data
    1. As with all the other SQL commands the UPDATE command follows a format similar to DELETE but it changes the columns in rows instead of deleting them.
    2. So let’s try the following code :

      [code language=”sql”]UPDATE person SET first_name = "Arjun"
      WHERE first_name = "Suresh";
      [/code]

      [code language=”sql”] UPDATE pet SET name = "Brandy"
      WHERE id=0;
      [/code]

      [code language=”sql”]SELECT * FROM person;[/code]

      [code language=”sql”]SELECT * FROM pet;[/code]
    3. 12

    4. In the above code we are changing name to “Arjun”.
    5. And renamed Lab to “Brandy”.
    6. This shouldn’t be that hard to figure out, but just in case we are going to break the first one down:
    7. Start with UPDATE and the table we are going to update, in this case person.
    8. Next use SET to say what columns should be set to what values. We can change as many columns as you want as long as you separate them with commas like first_name = “Suresh”, last_name = “Sharma”.
    9. Then specify a WHERE clause that gives a SELECT style set of tests to do on each row.
    10. When the UPDATE finds a match it does the update and SETs the columns to how we specified

  • Exercise 11 : Updating Complex Data
    1. In the last exercise we implemented query in the UPDATE, and now we’ll use it to change all the pets own to be named “Arjun’s Pet”.
    2. So now for changing all the pets own to be named “Arjun’s Pet” Implement the following code :

      [code language=”sql”] SELECT * FROM pet;[/code]

      [code language=”sql”]UPDATE pet SET name = "Arjun’s Pet" WHERE id IN (
      SELECT pet.id
      FROM pet, person_pet, person
      WHERE
      person.id = person_pet.person_id AND
      pet.id = person_pet.pet_id AND
      person.first_name = "Arjun"
      );[/code]

      [code language=”sql”]SELECT * FROM pet;[/code]
    3. 13

    4. This is how you update one table based on information from another table.
  • Exercise 12 : Replacing Data
    1. In this situation, we want to replace the record with another guy but keep the unique id.
    2. Another simpler way to do it is to use the REPLACE command, or add it as a modifier to INSERT.
    3. Here’s some SQL where you first fail to insert the new record, then you use these two forms of REPLACE to do it:

      •/* This should fail because 0 is already taken. */
      [code language=”sql”]INSERT INTO person (id, first_name, last_name, age)
      VALUES (0, ‘Frank’, ‘Smith’, 100);[/code]

      •/* We can force it by doing an INSERT OR REPLACE. */
      [code language=”sql”]INSERT OR REPLACE INTO person (id, first_name, last_name, age)
      VALUES (0, ‘Frank’, ‘Smith’, 100);[/code]

      [code language=”sql”]SELECT * FROM person;[/code]

      •/* And shorthand for that is just REPLACE. */
      [code language=”sql”]REPLACE INTO person (id, first_name, last_name, age)
      VALUES (0, ‘Zed’, ‘Shaw’, 37);[/code]

      •/* Now you can see Arjun is back. */
      [code language=”sql”]SELECT * FROM person;[/code]
    4. 14

  • Exercise 13 : Destroying And Altering Tables
    1. You’ve already encountered DROP TABLE as a way to get rid of a table you’ve created.
    2. Let’s see another way to use it and also how to add or remove columns from a table with ALTER TABLE.

      • /* Only drop table if it exists. */
      [code language=”sql”]DROP TABLE IF EXISTS person;[/code]

      • /* Create again to work with it. */
      [code language=”sql”]CREATE TABLE person (
      id INTEGER PRIMARY KEY,
      first_name TEXT,
      last_name TEXT,
      age INTEGER
      );
      [/code]

      • /* Rename the table to peoples. */
      [code language=”sql”]ALTER TABLE person RENAME TO peoples;[/code]

      • /* Add a hatred column to peoples. */
      [code language=”sql”]ALTER TABLE peoples ADD COLUMN hatred INTEGER;[/code]

      • /* Rename peoples back to person. */
      [code language=”sql”]ALTER TABLE peoples RENAME TO person;[/code]
    3. .schema person
    4. /* We don’t need that. */

      [code language=”sql”]DROP TABLE person;[/code]
    5. 15

    6. We are doing some fake changes to the tables to demonstrate the commands, but this is everything you can do in SQLite3 with the ALTER TABLE and DROP TABLE statements.
    7. Let’s go through this so you will understand what’s going on:

      Use the IF EXISTS modifier and the table will be dropped only if it’s already there.

      Just recreating the table again to work with it.

      Using ALTER TABLE to rename it to peoples.

      Add a new column hatred that is an INTEGER to the newly renamed table peoples.

      Rename peoples back to person because that’s a dumb name for a table.

      Dump the schema for person so you can see it has the new hatred column.

      Drop the table to clean up after this exercise.

  • Exercise 14 : Basic Transactions
    1. Imagine if the SQL in your last exercise had an error half-way through it’s run and it aborted.
    2. You may have even ran into this problem, and then you see that your database is not seriously broken, but in a real situation you can’t trash your whole database when you mess up.
    3. To make your script safer use the BEGIN, COMMIT, and ROLLBACK commands.
    4. These start a transaction, which creates a “boundary” around a group of SQL statements so you can abort them if they have an error.
    5. You start the transaction with BEGIN, do your SQL, and then when everything’s good end the transaction with COMMIT.
    6. If you have an error then you just issue ROLLBACK to abort what you did.
    7. Now lets try the following statements :

      [code language=”sql”]begin;
      insert into person values(0,”Arjun”,”Pandey”,36);
      insert into person values(0,”Rakesh”,”Sharma”,40);
      select * from person;
      rollback ;
      select * from person;
      commit ;
      [/code]
    8. 16

    9. Let’s go through this so you will understand what’s going on:

      Set the boundry by begin.

      Inserting the values in person table.

      Select the person table to display the contents.

      Now if we entered some wrong values and we want to discard that than we can use rollback command which sets your work to the normal form as it was before entering the begin command that is the boundry.

      If your work is correctly completed and no changes are to be done you can save your work by using the commit command.