dnms prac 2 Flashcards
query to update the values from existing column of table
update table_name set coloumn_name=value where condition
query to delete certain records ( rows ) from employee table
delete from table_name where condition
delete all rows from table_name
delete from table_name;
query to permanently delete
drop table table_name;
how to use aggregate function avg()
select avg(column_name) from table_name where condition ;
where condition optional
how to use concat()
function
select concat(col1, col2, ..) from table_name where condition
where condition is optional
what is use of like and tell it’s syntax
used to substring in string
ex name%, %ter%, %man
synatx :
select col1,…,* from table_name like ‘%substring%’;
use of in and syntax
in is used to check if certain value belongs in certain set of values or result of some query
select col1, … , * from table_name where col in (query / set of values);
tell 5 aggregate functions
max(col) , avg(col) , count (col) , concat (col1,col2,…, coln), max(col) , min(col) , ucase (col) , lcase(col), length(col)
use and syntax of using between
used to specify values between certain values
syntax :
select col1, … , coln from table_name where col between ‘val1’ and ‘val2’;
what are set operator in sql
union , union all , intersect , minus or except
what does union do ? Syntax
combines result sets of two or more select statements into single result and removes duplicate rows
select column_name1 from table1
UNION
select column_name2 from table2
UNION
select column_name3 from table 3
.
.
.
rules to use set operation
- Every select statement must have same number of columns
- Columns must also have similar datatypes
- The columns in select statement must also be in the same order.
what does union all do ?
to combine the result sets of two or more select statements into a single result set
including duplicate record;
what does except/minus operator do and syntax?
returns all records in query 1 which are not present in query 2
query1
minus / except
query2
