PRACTICAL 1: CRUD SQL OPERATION

PRACTICAL 1: CRUD SQL OPERATION

CRUD is an acronym for the four basic types of SQL commands: Create , Read , Update , Delete. CRUD is also sometimes used to describe user interface conventions that facilitate viewing, searching, and changing information; often using computer-based forms and reports.

1.      CREATE TABLE: The SQL Create Table statement is used to create new SQL database.

SYNTAX: Basic syntax of creating table is as follows.

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:

Figure 1-creating table

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.

The empty "Persons" table will now look like this:

Figure 2-table"Person"

2.      INSERTING VALUES: The INSERT INTO statement is used to insert new records in a table.

SYNTAX: 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. 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 "Customers" table:

Figure 3-Inserting Values

3.      SELECTING VALUES: The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

SYNTAX:

SELECT column1, column2, ...

FROM table_name;

Here, column1, column2, ... are the field names of the table you want to select data from.

Figure 4-Selecting customer column

Figure 5-selecting customer name from Table

4.    UPDATING VALUES: The UPDATE statement is used to modify the existing records in a table.

SYNTAX:

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

Figure 6-Updating "Customer"

The selection from the "Customers" table will now look like this:

Figure 7-Updated value

5.      DELETING VALUES: The DELETE statement is used to delete existing records in a table

SYNTAX:

Figure 8-Deleting values from table

. The "Customers" table will now look like this:

Figure 9- New table after deletion of value