Scribd
Upload
102 views7 pages

Resumen SQL

This document provides an overview of SQL commands and functions including: - SELECT statements to retrieve data from tables - COUNT, DISTINCT, and LIMIT functions - Data manipulation commands like INSERT, UPDATE, DELETE - Data definition commands like CREATE, ALTER, DROP, TRUNCATE - Aggregate functions such as SUM, MAX, AVG - Scalar and string functions like ROUND, LENGTH, UCASE, LCASE - Date and time functions - Using subqueries and nested SELECT statements to access data from multiple tables

Uploaded by

gia fer
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
102 views7 pages

Resumen SQL

This document provides an overview of SQL commands and functions including: - SELECT statements to retrieve data from tables - COUNT, DISTINCT, and LIMIT functions - Data manipulation commands like INSERT, UPDATE, DELETE - Data definition commands like CREATE, ALTER, DROP, TRUNCATE - Aggregate functions such as SUM, MAX, AVG - Scalar and string functions like ROUND, LENGTH, UCASE, LCASE - Date and time functions - Using subqueries and nested SELECT statements to access data from multiple tables

Uploaded by

gia fer
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

SQL

Contenido
SELECT................................................................................................................................................1
Count..................................................................................................................................................1
Distinct...............................................................................................................................................2
LIMIT..................................................................................................................................................2
Insert..................................................................................................................................................2
Update...............................................................................................................................................2
Delete.................................................................................................................................................3
Create.................................................................................................................................................3
Alter...................................................................................................................................................3
Truncate.............................................................................................................................................4
Drop...................................................................................................................................................4
Aggregate Functions SUM – MAX – AVG............................................................................................4
Scalar and String Functions ROUND – LENGHT – UCASE – LCASE- DISTINCT......................................5
Date and Time Functions....................................................................................................................5
Sub-queries and Nested SELECTs.......................................................................................................5
Accessing Multiple Tables with Sub-Queries......................................................................................6
Accessing Multiple Tables with Implicit Joins.....................................................................................6

SELECT

SELECT * FROM FilmLocations;

SELECT Title, Director, Writer FROM FilmLocations;

SELECT column1, column2, ...

FROM table_name

WHERE condition

Count
SELECT COUNT(*) FROM FilmLocations;

In this example, suppose we want to count the number of records or rows of the “FilmLocations”
table.

SELECT COUNT(Locations) FROM FilmLocations WHERE Writer="James Cameron";

In this example, now we want to count the number of locations of the films. But we also want to
restrict the output resultset in such a way that we only retrieve the number of locations of the
films written by a certain writer.
Distinct
SELECT DISTINCT Title FROM FilmLocations;

In this example, we want to retrieve the title of all films in the table in such a way that duplicates
will be discarded in the output resultset.

SELECT COUNT(DISTINCT ReleaseYear) FROM FilmLocations WHERE ProductionCompany="Warner


Bros. Pictures";

In this example, we want to retrieve the count of release years of the films produced by a specific
company in such a way that duplicate release years of those films will be discarded in the count.

LIMIT
SELECT * FROM FilmLocations LIMIT 25;

Retrieve the first 25 rows from the “FilmLocations” table.

SELECT * FROM FilmLocations LIMIT 15 OFFSET 10;

Retrieve the first 15 rows from the “FilmLocations” table starting from row 11.

Insert
INSERT INTO table_name (column1, column2, ... )

VALUES (value1, value2, ... )

INSERT INTO Instructor(ins_id, lastname, firstname, city, country)

VALUES(4, 'Saha', 'Sandip', 'Edmonton', 'CA');

INSERT INTO PET VALUES


(1,'Cat',3),
(2,'Dog',4),
(3,'Hamster',2);

Update
UPDATE table_name

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

WHERE condition

UPDATE Instructor

SET city='Toronto'
WHERE firstname="Sandip";

Update the city for Sandip to Toronto.

UPDATE Instructor

SET city='Dubai', country='AE'

WHERE ins_id=5;

Update the city and country for Doe with id 5 to Dubai and AE respectively.

UPDATE PETSALE SET QUANTITY = 9 WHERE ID = 1;


UPDATE PETSALE SET QUANTITY = 3 WHERE ID = 2;
UPDATE PETSALE SET QUANTITY = 2 WHERE ID = 3;

Delete
DELETE FROM table_name

WHERE condition

DELETE FROM instructor

WHERE ins_id = 6;

Remove the instructor record of Doe whose id is 6.

Create
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
CREATE TABLE PETSALE (
ID INTEGER NOT NULL,
PET CHAR(20),
SALEPRICE DECIMAL(6,2),
PROFIT DECIMAL(6,2),
SALEDATE DATE
);

CREATE TABLE PET (


ID INTEGER NOT NULL,
ANIMAL VARCHAR(20),
QUANTITY INTEGER
);

Alter
ALTER TABLE table_name
ADD COLUMN column_name data_type column_constraint;

ALTER TABLE table_name


DROP COLUMN column_name;
ALTER TABLE table_name
ALTER COLUMN column_name SET DATA TYPE data_type;

ALTER TABLE table_name


RENAME COLUMN current_column_name TO new_column_name;

ALTER TABLE PETSALE


ADD COLUMN QUANTITY INTEGER;
ALTER TABLE PETSALE
DROP COLUMN PROFIT;

Delete the PROFIT column from the PETSALE table

ALTER TABLE PETSALE CHANGE PET PET VARCHAR(20);

Change the data type to VARCHAR(20) type of the column PET of the table PETSALE

ALTER TABLE `PETSALE` CHANGE `PET` `ANIMAL` varchar(20);

Rename the column PET to ANIMAL of the PETSALE table and show the altered table. 

Truncate

TRUNCATE TABLE table_name;

TRUNCATE TABLE PET ;

Remove all rows from the PET table

Drop
DROP TABLE table_name;

DROP TABLE PET;


Delete the PET table

Aggregate Functions SUM – MAX – AVG

Query A1: Enter a function that calculates the total cost of all animal rescues in the PETRESCUE
table.

select SUM(COST) from PETRESCUE;

Query A2: Enter a function that displays the total cost of all animal rescues in the PETRESCUE table
in a column called SUM_OF_COST.

select SUM(COST) AS SUM_OF_COST from PETRESCUE;

Query A3: Enter a function that displays the maximum quantity of animals rescued.

select MAX(QUANTITY) from PETRESCUE;

Query A4: Enter a function that displays the average cost of animals rescued.

select AVG(COST) from PETRESCUE;

Query A5: Enter a function that displays the average cost of rescuing a dog. Hint - Bear in my the
cost of rescuing one dog on day, is different from another day. So you will have to use and average
of averages.
select AVG(COST/QUANTITY) from PETRESCUE where ANIMAL = 'Dog';

Scalar and String Functions ROUND – LENGHT – UCASE –


LCASE- DISTINCT

Query B1: Enter a function that displays the rounded cost of each rescue.

select ROUND(COST) from PETRESCUE;

Query B2: Enter a function that displays the length of each animal name.

select LENGTH(ANIMAL) from PETRESCUE;

Query B3: Enter a function that displays the animal name in each rescue in uppercase.

select UCASE(ANIMAL) from PETRESCUE;

Query B4: Enter a function that displays the animal name in each rescue in uppercase without
duplications.

select DISTINCT(UCASE(ANIMAL)) from PETRESCUE;

Query B5: Enter a query that displays all the columns from the PETRESCUE table, where the
animal(s) rescued are cats. Use cat in lower case in the query.

select * from PETRESCUE where LCASE(ANIMAL) = 'cat';

Date and Time Functions

Query C1: Enter a function that displays the day of the month when cats have been rescued.

select DAY(RESCUEDATE) from PETRESCUE where ANIMAL = 'Cat';

Query C2: Enter a function that displays the number of rescues on the 5 th month.

select SUM(QUANTITY) from PETRESCUE where MONTH(RESCUEDATE)='05';

Query C3: Enter a function that displays the number of rescues on the 14 th day of the month.

select SUM(QUANTITY) from PETRESCUE where DAY(RESCUEDATE)='14';

Query C4: Animals rescued should see the vet within three days of arrivals. Enter a function that
displays the third day from each rescue.

select (RESCUEDATE + 3 DAYS) from PETRESCUE;

Query C5: Enter a function that displays the length of time the animals have been rescued; the
difference between today’s date and the recue date.

select (CURRENT DATE - RESCUEDATE) from PETRESCUE;

Sub-queries and Nested SELECTs


Execute a working query using a sub-select to retrieve all employees records whose salary is lower than the
average salary.

select EMP_ID, F_NAME, L_NAME, SALARY


from employees
where SALARY < (select AVG(SALARY)
from employees);

Execute a Column Expression that retrieves all employees records with EMP_ID, SALARY and maximum salary as
MAX_SALARY in every row.
select EMP_ID, SALARY, ( select MAX(SALARY) from employees ) AS MAX_SALARY
from employees;

Execute a Table Expression for the EMPLOYEES table that excludes columns with sensitive employee data (i.e. does
not include columns: SSN, B_DATE, SEX, ADDRESS, SALARY).

select * from ( select EMP_ID, F_NAME, L_NAME, DEP_ID from employees) AS


EMP4ALL;

Accessing Multiple Tables with Sub-Queries

Retrieve only the EMPLOYEES records that correspond to jobs in the JOBS table.

select * from employees where JOB_ID IN (select JOB_IDENT from jobs);

Retrieve only the list of employees whose JOB_TITLE is Jr. Designer.

select * from employees where JOB_ID IN (select JOB_IDENT from jobs where
JOB_TITLE= 'Jr. Designer');

Retrieve JOB information and who earn more than $70,000.

select JOB_TITLE, MIN_SALARY,MAX_SALARY,JOB_IDENT from jobs where JOB_IDENT


IN (select JOB_ID from employees where SALARY > 70000 );

Retrieve JOB information for female employees whose birth year is after 1976.

select JOB_TITLE, MIN_SALARY,MAX_SALARY,JOB_IDENT from jobs where JOB_IDENT


IN (select JOB_ID from employees where YEAR(B_DATE)>1976 and SEX='F' );

Accessing Multiple Tables with Implicit Joins


Perform an implicit cartesian/cross join between EMPLOYEES and JOBS tables.

select * from employees, jobs;


Retrieve only the EMPLOYEES records that correspond to jobs in the JOBS table.

select * from employees, jobs where employees.JOB_ID = jobs.JOB_IDENT;

Redo the previous query, using shorter aliases for table names.

select * from employees E, jobs J where E.JOB_ID = J.JOB_IDENT;

Redo the previous query, but retrieve only the Employee ID, Employee Name and Job Title.

select EMP_ID,F_NAME,L_NAME, JOB_TITLE from employees E, jobs J where


E.JOB_ID = J.JOB_IDENT;

Redo the previous query, but specify the fully qualified column names with aliases in the SELECT clause.

select E.EMP_ID,E.F_NAME,E.L_NAME, J.JOB_TITLE from employees E, jobs J


where E.JOB_ID = J.JOB_IDENT;

You might also like