Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

Query SQL

memorize.ai (lvl 286)

(0)

76 cards

()

Section 1

Preview this deck

Using Comments

Front

1 / 76

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

4 years ago

Date created

Mar 1, 2020

Cards (76)

Section 1

(50 cards)

Using Comments

Front

/* SELECT prod_name, vend_id FROM Products; */

Back

Using the WHERE Clause

Front

SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;

Back

Retrieving Multiple Columns

Front

SELECT prod_id, prod_name,prod_price FROM Products;

Back

The Brackets ([]) Wildcard

Front

SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;

Back

Operator

Front

A special keyword used to join or change clauses within a WHERE clause. Also known as logical operators.

Back

Database

Front

A container (usually a file or set of files) to store organized data.

Back

The MIN() Function

Front

SELECT MIN(prod_price) AS min_price FROM Products;

Back

Performing Mathematical Calculations

Front

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

Back

Limiting Results

Front

SELECT TOP 5 prod_name FROM Products;

Back

Schema

Front

Information about database and table layout and properties.

Back

Concatenating Fields

Front

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' FROM Vendors ORDER BY vend_name;

Back

Concatenating Fields

Front

SELECT Concat(vend_name, ' (', vend_country, ')') FROM Vendors ORDER BY vend_name;

Back

The Brackets ([]) Wildcard

Front

SELECT cust_contact FROM Customers WHERE NOT cust_contact LIKE '[JM]%' ORDER BY cust_contact;

Back

The Percent Sign (%) Wildcard

Front

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%bean bag%';

Back

Retrieving Distinct Rows

Front

SELECT DISTINCT vend_id FROM Products;

Back

Row

Front

A record in a table.

Back

Table

Front

A structured list of data of a specific type.

Back

The Percent Sign (%) Wildcard

Front

SELECT prod_name FROM Products WHERE prod_name LIKE 'F%y';

Back

Concatenating Fields

Front

SELECT vend_name || ' (' || vend_country || ')' FROM Vendors ORDER BY vend_name;

Back

Using Comments

Front

-- this is a comment

Back

The SUM() Function

Front

SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE order_num = 20005;

Back

Date and Time Manipulation Functions

Front

SELECT order_num FROM Orders WHERE order_date BETWEEN to_date('01-01-2012') AND to_date('12-31-2012');

Back

Keyword

Front

A reserved word that is part of the SQL language.

Back

Using the NOT Operator

Front

SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;

Back

Sorting Data

Front

SELECT prod_name FROM Products ORDER BY prod_name;

Back

Using the AND Operator

Front

SELECT prod_id, prod_price,prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;

Back

Concatenating Fields

Front

SELECT vend_name + ' (' + vend_country + ')' FROM Vendors ORDER BY vend_name;

Back

Retrieving All Columns

Front

SELECT * FROM Products;

Back

The SUM() Function

Front

SELECT SUM(item_price*quantity) AS total_price FROM OrderItems WHERE order_num = 20005;

Back

The COUNT() Function

Front

SELECT COUNT(*) AS num_cust FROM Customers;

Back

Datatype

Front

A type of allowed data. Every table column has an associated datatype that restricts (or allows) specific data in that column.

Back

Using the OR Operator

Front

SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

Back

The MAX() Function

Front

SELECT MAX(prod_price) AS max_price FROM Products;

Back

The Percent Sign (%) Wildcard

Front

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';

Back

Primary key

Front

A column (or set of columns) whose values uniquely identify every row in a table.

Back

Specifying Sort Direction

Front

SELECT prod_id, prod_price,prod_name FROM Products ORDER BY prod_price DESC;

Back

Sorting by Column Position

Front

SELECT prod_id, prod_price,prod_name FROM Products ORDER BY 2, 3;

Back

Retrieving Individual Columns

Front

SELECT prod_name FROM Products;

Back

Using the IN Operator

Front

SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01') ORDER BY prod_name;

Back

SQL

Front

Is an abbreviation for Structured Query Language. SQL is a language designed specifically for communicating with databases.

Back

The AVG() Function

Front

SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

Back

Using Aliases

Front

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title FROM Vendors ORDER BY vend_name;

Back

Text Manipulation Functions

Front

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors ORDER BY vend_name;

Back

The Underscore (_) Wildcard

Front

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear';

Back

Using Comments

Front

# This is a comment

Back

Date and Time Manipulation Functions

Front

SELECT order_num FROM Orders WHERE DATEPART(yy, order_date) = 2012;

Back

AND

Front

A keyword used in a WHERE clause to specify that only rows matching all the specified conditions should be retrieved.

Back

Column

Front

A single field in a table. All tables are made up of one or more columns.

Back

Sorting by Multiple Columns

Front

SELECT prod_id, prod_price,prod_name FROM Products ORDER BY prod_price, prod_name;

Back

Text Manipulation Functions

Front

SELECT cust_name, cust_contact FROM Customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

Back

Section 2

(26 cards)

Filtering by Subquery

Front

SELECT cust_name, cust_state, (SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id) AS orders FROM Customers ORDER BY cust_name;

Back

Inner Joins

Front

SELECT cust_name, cust_contact FROM Customers, Orders, OrderItems WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num

Back

Grouping and Sorting

Front

SELECT order_num, COUNT() AS items FROM OrderItems GROUP BY order_num HAVING COUNT() >= 3 ORDER BY items, order_num;

Back

--Buscador de historial de animal por nombre

Front

ALTER PROCEDURE historialclinica @palabra smallint as SELECT cod_animal,comenzado, nombre_animal, nombre_pila,comentarios,nombres,apellidos FROM historial WHERE cod_animal = @palabra ORDER BY comenzado EXEC historialclinica 1001

Back

Creating a Join

Front

SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id;

Back

Combining Aggregate Functions

Front

SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM Products;

Back

Aggregates on Distinct Values

Front

SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

Back

Natural Joins

Front

Back

Joining Multiple Tables

Front

SELECT prod_name, vend_name, prod_price, quantity FROM OrderItems, Products, Vendors WHERE Products.vend_id = Vendors.vend_id AND OrderItems.prod_id = Products.prod_id AND order_num = 20007;

Back

Self Joins

Front

SELECT c1.cust_id, c1.cust_name, c1.cust_contact FROM Customers AS c1, Customers AS c2 WHERE c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones';

Back

Self Joins

Front

SELECT cust_id, cust_name, cust_contact FROM Customers WHERE cust_name = (SELECT cust_name FROM Customers WHERE cust_contact = 'Jim Jones');

Back

Outer Joins

Front

SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

Back

Vista animal y habitat

Front

alter view animalxhabitat with encryption as select count(ae.cod_animal) as cantidad_Actual,ae.cod_habitat, t.capacidad from animal_Especifico ae INNER JOIN habitat t on t.cod_habitat=ae.cod_habitat group by ae.cod_habitat,t.capacidad select * from animalxhabitat

Back

Using Table Aliases, Inner Joins

Front

SELECT cust_name, cust_contact FROM Customers AS C, Orders AS O, OrderItems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';

Back

Vista de nombre de cuidador y animal

Front

ALTER view animalxcuidador with encryption as select e.nombres,e.apellidos,e.género,count(s.cod_animal) as CantidadAsignado from supervisa s INNER JOIN Empleado e on e.cod_empleado=s.cod_empleado group by e.nombres,e.apellidos,e.género

Back

Filtering Groups

Front

SELECT cust_id, COUNT() AS orders FROM Orders GROUP BY cust_id HAVING COUNT() >= 2;

Back

Filtering by Subquery

Front

SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01';

Back

display the total number of orders placed by every customer in your Customers

Front

SELECT cust_name,cust_state,(SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers ORDER BY cust_name;

Back

Filtering by Subquery

Front

SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01');

Back

Creating Groups

Front

SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id;

Back

vista

Front

create view animalespecie with encryption as select especie as especie, nro_animal=count(cod_animal) from animal_Especifico group by especie

Back

Grouping and Sorting

Front

SELECT order_num, COUNT() AS items FROM OrderItems GROUP BY order_num HAVING COUNT() >= 3;

Back

Inner Joins

Front

SELECT vend_name, prod_name, prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

Back

Filtering Groups

Front

SELECT vend_id, COUNT() AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT() >= 2;

Back

Filtering by Subquery

Front

SELECT cust_name, cust_contact FROM Customers WHERE cust_id IN (SELECT cust_id FROM Order WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01'));

Back

vista

Front

create view animalmuerto with encryption as select ae.nombre_animal as nombre,ae.especie,m.causa_muerte as causa, RTRIM(e.nombres)+ ' ' + RTRIM(e.apellidos)as veterinario from animal_Especifico ae INNER JOIN muerte m ON ae.cod_animal=m.cod_animal INNER JOIN Tratamiento t ON t.cod_animal=m.cod_animal INNER JOIN Empleado e ON e.cod_empleado=t.cod_empleado

Back