Solve SQL
Write the SQL command
Update command
• The update command specifies the rows to be changes using the
WHERE clause , and the new data using the SET keyword
• Update <table name> set <column name=value> where <condition>
• Update exam set score=score+2 where subj_code=‘eng’
• Multiple columns
• Update items SET ROL=400, maths=90 WHERE icode< ‘i040’
Delete command
• The DELETE command removes rows from a table. This removes the
entire rows, not the individual filed values, so no filed argument is
needed or accepted. The DELETE statement takes the following
general form:
• DELETE FROM <tablename> WHERE <predicate>;
• DELETE FROM items
• DELETE FROM employee where gross<2200;
ALTER table command
• The ALTER TABLE command is used to change definitions of existing
tables. Usually, it can add columns to a table. Sometimes it can delete
columns (depending on privileges) or change their sizes.
• ALTER TABLE<table name> ADD <Column name> <data type> <size>
[<constraint name>]
• With MODIFY clause you can also reorder the column within the
table. For this you need to use FIRST or AFTER.
• ALTER TABLE <tablename> MODIFY (columnname newdatatype
(newsize))[FIRST | AFTER column];
• To modify table column datatype
• ALTER TABLE Assignment MODIFY projid INT
Group by
• Group by clause combines all those records that have identical values
in a particular filed or a group of fields.
• Select job, count(*) FROM emp1 GROUPBY job;
• Select deptno, count(*), sum(sal) from emp1 GROUP BY deptno;
• Select count(empno) from GROUP BY deptno;
• Select deptno, count(empno),mgr from emp1 group by deptno;
Placing conditions on Groups
• The HAVING clause places condition on groups in contrast to WHERE
clause that place conditions on individual rows. While WHERE conditions
cannot include aggregate functions, HAVING condition can do so.
• SELECT Deptno, AVG(gross), sum(gross) from employee GROUP BY grade
having grade=‘E4’
Given a table named students with columns name and email, write an SQL
query to update the email addresses of students whose names are Paul and Peter.
Use the following email IDs: paul@[Link] and peter@[Link]
update student set email='paul@[Link]' where sname='paul’;
Select the data having ingcategoryID less than 4
Non Group Expression with Group BY
• When using GROUP BY in SQL, only grouped columns and aggregate
functions (like SUM(), AVG(), MAX() etc.) are allowed in the SELECT
[Link] you try to include a column that is not part of the GROUP BY
clause or not inside an aggregate function
• Example for aggregate function:
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
non-aggregated column in GROUP BY:
SELECT department, employee_name, AVG(salary)
FROM employees
GROUP BY department, employee_name;
Join syntax
SELECT [Link], [Link]
FROM table1
JOIN table2
ON table1.common_column = table2.common_column;
SQL joins
• Used to combine 2 tables:
• Inner and left join
Inner join combines 2 tables which matches values:
Left join
Reordering columns in query result
• Select species, name, gender, from pet
Eliminating redundant data (with keyword
Distinct)
• Select Distinct city from supplier
Selecting from all the Rows – All keyword
• Select all city from supplier
Scalar expression with selected fields
• Select salesman_name, comm*100 from salesman;
Using column aliases
• Select date, type as “event type” from event;
Relational operators
• The compare two values, a relational operator is used. The result of
the comparison is true or false. The SQL recognizes following
relational operators
• Select * from student where Grno<>103;
• Select name, emailed from teachers where e_id>103;
Logical operator
• The logical operators OR(||) , AND (&&)and NOT(!) are used to
connect search conditions in the where clause
• Select eid, ename, email from employee where(depart=‘cs’ OR
depart=‘HR’);
• Select eid, ename, email from employee where(depart=‘cs’ and
salaryamount>2400);
• Select eid, ename, email from employee where((depart=‘cs’
||depart=‘HR’) && salaryamount>2400);
Join
• A join is a query that combines rows from two or more tables. In a
join-query, more than one table are listed in From clause.
• Select patient_no, description , normal_charge, charge from
billed,item where billed.item_code=item.item_code;
joins
• SELECT [Link], [Link], [Link] FROM
Patients JOIN Doctors ON [Link] = [Link];
• SELECT * FROM Doctors WHERE NoofOpdDays > 3;
• SELECT [Link], [Link], [Link],
[Link] FROM Doctors JOIN Patients ON
[Link]=[Link] WHERE [Link] IN (101, 103);
• SELECT COUNT(DISTINCT Department) AS TotalDepartments FROM Patients;
Cartesian product
• Select * from emp, student;
• This query will give you the cartesian product all possible
concatenations are formed of all rows of both the tables EMP and
student
• Table1 -Emp ->20 rows (n1)
• Table2- student ->30 rows(n2)
• Cartesian product =n1( no of rows in table1) X n2 ( no of rows in
table2)
• Cartesian product=20*30
Equi join and Natural Join
• The join in which columns are compared for equality, is called equi-
join.
• A non-equi join is a query that specifies some relationship other than
equality between the columns.
• The join in which only one of the identical columns ( coming from
joined tables) exists, is called natural join.