Inserting ,Updaing and Deleting data in database

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

 If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).

SQL CREATE TABLE + CONSTRAINT Syntax

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

 In SQL, we have the following constraints:

• NOT NULL - Indicates that a column cannot store NULL value
• UNIQUE - Ensures that each row for a column must have a unique value
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
• FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
• CHECK - Ensures that the value in a column meets a specific condition
• DEFAULT - Specifies a default value when specified none for this column

CREATE TABLE CUSTOMER

(custno varchar(10) primary key,

cname varchar(20) not null,

cadd varchar(30),

city varchar(20),

pincode integer,

state varchar(20),

creditlimit integer);

INSERT INTO CUSTOMER VALUES('C1','AKASH SINGH','MAIN STREET','DEHLI',239628,'HARIYANA',50000);

INSERT INTO CUSTOMER VALUES('C2','MAYUR RAI','RANADE ROAD','MUMBAI',400012,'MAHARASHTRA',45000);

INSERT INTO CUSTOMER VALUES('C3','SPARSH MEHTA','KAROL BAUG','DEHLI',678452,'HARIYANA',70000);

INSERT INTO CUSTOMER VALUES('C4','KUNAL THAKARE','LINK ROAD','MUMBAI',400032,'MAHARASHTRA',20000);

INSERT INTO CUSTOMER VALUES('C5','PANKAJ AKRE','SAWARKAR MARG','BANGLORE',223897,'KARNATAK',55000);

The SQL UPDATE Statement

The UPDATE statement is used to update existing records in a table.

SQL UPDATE Syntax

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

 WHERE clause in the SQL UPDATE statement-
The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

The SQL DELETE Statement

The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value;

WHERE clause in the SQL DELETE statement.
The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

The ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

SQL ALTER TABLE Syntax

To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLE table_name
DROP COLUMN column_name

To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype

The SQL AND & OR Operators

The AND operator displays a record if both the first condition AND the second condition are true.

The OR operator displays a record if either the first condition OR the second condition is true.

AND Operator Example

The following SQL statement selects all customers from the country "Germany" AND the city "Berlin", in the "Customers" table:

Example

SELECT * FROM Customers
WHERE Country='Germany'
AND City='Berlin';

OR Operator Example

The following SQL statement selects all customers from the city "Berlin" OR "München", in the "Customers" table: 

Example

SELECT * FROM Customers
WHERE City='Berlin'
OR City='München';

Combining AND & OR

You can also combine AND and OR (use parenthesis to form complex expressions).

The following SQL statement selects all customers from the country "Germany" AND the city must be equal to "Berlin" OR "München", in the "Customers" table:

Example

SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');

Q1. ADD A NEW FIELD CALLED 'ADDRESS2' TO THE CUSTOMER TABLE

ALTER TABLE CUSTOMER

ADD ADDRESS2 VARCHAR(30);

SELECT * FROM CUSTOMER;

Q2. ADD A NEW FIELD 'TELEPHONE' OF DATATYPE INTEGER TO HTE SALESMANMASTER TABLE

ALTER TABLE SALESMASTER

ADD TELEPHONE INTEGER;

SELECT * FROM SALESMASTER;

Q3. CHANGE THE CITY OF CUSTOMER NO. 'C4' TO 'BANGLORE'

UPDATE CUSTOMER

SET CITY='BANGLORE'

WHERE custno='C4';

SELECT * FROM CUSTOMER;

Q4. CHANGE THE COSTPRICE OF 'TROUSERS' TO Rs.500

UPDATE PRODUCT

SET COSTPRICE=500

WHERE DESCRIPTION='TROUSERS';

SELECT * FROM PRODUCT;

Q5. DELETE ALL THE PRODUCTS FROM PRODUCTMASTER WHERE SELLPRICE=Rs.500

DELETE FROM PRODUCT

WHERE SELLPRICE=500;

SELECT * FROM PRODUCT;