EX:03 Update,Delete,Select

AIM:

To Study and Implement DML Commands.

THEORY:

INSERT INTO: This is used to add records into a relation.

These are three type of INSERT INTO queries which are as

a) Inserting a single record

Syntax: INSERT INTO < relation/table name> (field_1,field_2……field_n)VALUES (data_1,data_2, ...... data_n);student(sno,sname,class,address)VALUES (1,’Ravi’,’M.Tech’,’Palakol’);

b) Inserting all records from another relation

Syntax: INSERT INTO relation_name_1 SELECT Field_1,field_2,field_n FROM relation_name_2 WHERE field_x=data;

c) Inserting multiple records

Syntax: INSERT INTO relation_name field_1,field_2, ....field_n) VALUES (&data_1,&data_2,.......&data_n);

UPDATE-SET-WHERE: This is used to update the content of a record in a relation.

Syntax: SQL>UPDATE relation name SET Field_name1=data,field_name2=data, WHERE field_name=data; Example: SQL>UPDATE student SET sname = ‘kumar’ WHERE sno=1;

DELETE-FROM: This is used to delete all the records of a relation but it will retain the structure of that relation.

a) DELETE-FROM: This is used to delete all the records of relation. Syntax: DELETE FROM relation_name;

b) DELETE -FROM-WHERE: This is used to delete a selected record from a relation. Syntax:DELETE FROM relation_name WHERE condition;

4)SELECT FROM: To display a set of fields for all records of relation

Syntax: SELECT (column1,column2) FROM (Table Name)WHERE condition;

1.Write a SQL query to find customers who are either from the city 'New York' or who have a grade greater than 200. Return customer_id, cust_name, city, grade, and salesman_id.

Query:

SELECT customer_id, cust_name, city, grade, salesman_id
FROM customer
WHERE city = 'New York' OR grade > 200;

Output:

2.write a SQL query to find customers who are either from the city 'New York' or who do not have a grade greater than 100. Return customer_id, cust_name, city, grade, and salesman_id.

Query:

SELECT customer_id, cust_name, city, grade, salesman_id
FROM customer
WHERE city = 'New York' OR grade <= 100;

Output:

3.Write a SQL query to locate the details of customers with grade values above 100. Return customer_id, cust_name, city, grade, and salesman_id.

Query:

SELECT customer_id, cust_name, city, grade, salesman_id
FROM customer
WHERE grade > 100;

Output:

4.Write a SQL statement to retrieve city of all customers from customers table without any repeats.

Query:

SELECT DISTINCT city
FROM customers;

Output:

5.Write a SQL statement to Increase the selling price by 15% in the products table where quantity in stock is less than 50 and supplier ID is 10.

Query:

UPDATE Products
SET sell_price = sell_price * 1.15
WHERE quantity < 50 AND supplier_id = 10;

Output:

6.Write a SQL statement to double the availability of the product with product_id 1.

Query:

UPDATE Products
SET availability = availability * 2
WHERE product_id = 1;

Output:

7.Write a SQL query to reduce the reorder level by 30% where cost price is more than 50 and quantity in stock is less than 100 in the products table.

Query:

UPDATE products SET reorder_lvl = reorder_lvl-(reorder_lvl * 0.3) 
WHERE cost_price > 50 AND quantity < 100;

Output:

8.Write a SQL query to Delete All Doctors with a NULL Last Name

Query:

DELETE FROM Doctors
WHERE Last_Name IS NULL;

Output:

9.Write a SQL query to Delete customers from 'customer' table where 'CUST_CITY' is not 'New York' and 'OUTSTANDING_AMT' is greater than 5000.

Query:

DELETE FROM Customer
WHERE CUST_CITY <> 'New York' AND OUTSTANDING_AMT > 5000;

Output:

10.Write a SQL query to delete a doctor from Doctors table whos specialization is 'Cardiology'

Query:

DELETE FROM Doctors
WHERE specialization = 'Cardiology';

Output:

Result:

Therefore these are some example o implement DML Commands.

syedmokthiyar / dbms--ex-03 Goto Github PK

dbms--ex-03's Introduction

EX:03 Update,Delete,Select

AIM:

1.Write a SQL query to find customers who are either from the city 'New York' or who have a grade greater than 200. Return customer_id, cust_name, city, grade, and salesman_id.

Query:

Output:

2.write a SQL query to find customers who are either from the city 'New York' or who do not have a grade greater than 100. Return customer_id, cust_name, city, grade, and salesman_id.

Query:

Output:

3.Write a SQL query to locate the details of customers with grade values above 100. Return customer_id, cust_name, city, grade, and salesman_id.

Query:

Output:

4.Write a SQL statement to retrieve city of all customers from customers table without any repeats.

Query:

Output:

5.Write a SQL statement to Increase the selling price by 15% in the products table where quantity in stock is less than 50 and supplier ID is 10.

Query:

Output:

6.Write a SQL statement to double the availability of the product with product_id 1.

Query:

Output:

7.Write a SQL query to reduce the reorder level by 30% where cost price is more than 50 and quantity in stock is less than 100 in the products table.

Query:

Output:

8.Write a SQL query to Delete All Doctors with a NULL Last Name

Query:

Output:

9.Write a SQL query to Delete customers from 'customer' table where 'CUST_CITY' is not 'New York' and 'OUTSTANDING_AMT' is greater than 5000.

Query:

Output:

10.Write a SQL query to delete a doctor from Doctors table whos specialization is 'Cardiology'

Query:

Output:

Result:

dbms--ex-03's People

Contributors

Watchers

Recommend Projects

Recommend Topics

Recommend Org