Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

42 cards

()

Section 1

Preview this deck

contains T

Front

1 / 42

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

7 years ago

Date created

Mar 1, 2020

Cards (42)

Section 1

(42 cards)

contains T

Front

LIKE '%T%'

Back

A subquery can occur in

Front

a select, a from, or a where

Back

List all customers with their total number of orders

Front

SELECT FirstName, LastName, OrderCount = (SELECT COUNT(O.Id) FROM [Order] O WHERE O.CustomerId = C.Id) FROM Customer C

Back

What would happen in this query SELECT c.customerid, c.companyName, orderid FROM customers c FULL OUTER JOIN orders o ON o.customerid = c.customerid ORDER BY orderid

Front

Every customerid and company name would be shown, every orderid would be shown and every instance where there is a match

Back

Order by

Front

SELECT columnname FROM tablename ORDER BY columnname;

Back

HAVING is used because

Front

WHERE does not work on aggregate functions

Back

group by queries return

Front

a single row for each grouped item

Back

Step 2 of Parameterized SQL Commands

Front

create and set query variable of type string to house SQL insert command

Back

HAVING is used to

Front

restrict what is returned by the group by clause

Back

Subquery in the FROM clause

Front

SELECT columnname FROM (SELECT columnname FROM tablename1) AS sub1

Back

Group By

Front

used in aggregate functions to return results for each distinct row in the column specified

Back

Joining more than 2 tables

Front

SELECT columnname FROM tablename1 INNER JOIN tablname2 ON tablename1.pk=tablename2.FK JOIN tablename3 ON tbalename2.FK=tablename3.PK

Back

Returns average bonus from each territory by average bonus lowest to highest

Front

SELECT TerritoryID, AverageBonus FROM (SELECT TerritoryID, Avg(Bonus) AS AverageBonus FROM Sales.SalesPerson GROUP BY TerritoryID) AS TerritorySummary ORDER BY AverageBonus

Back

Order by is used to

Front

sort results by ascending or descending order

Back

Group By with Having

Front

SELECT columnname1, aggregatefunction(columnname2) FROM tablename GROUP BY columnname1 HAVING columnname=10;

Back

INNER JOIN Returns Table 1: Orders Order ID Customer ID 1 4 2 5 3 6 Table 2: Customer Customer ID Name 4 Matt 5 Jeff 6 Craig

Front

OrderID Customer Name 1 Matt 2 Jeff 3 Craig

Back

What would happen in this query SELECT c.customerid, c.companyName, orderid FROM customers c RIGHT JOIN orders o ON o.customerid = c.customerid ORDER BY orderid

Front

Every orderid from the order table would be shown and the customer id and company name that matches it

Back

which query executes first in a subquery?

Front

the inner query

Back

you can only use the having clause

Front

with a group by

Back

starts with T

Front

LIKE 'T%'

Back

Group By clause

Front

SELECT columnname1, aggregatefunction(columnname2) FROM tablename GROUP BY columnname1;

Back

Select with WHERE

Front

SELECT columnname FROM tablename WHERE columnname=10;

Back

SELECT DISTINCT

Front

SELECT DISTINCT columnname FROM tablename

Back

Subquery in WHERE clause

Front

SELECT columnname1 FROM tablename1 WHERE columnname2 IN (SELECT columnname2 FROM tablename2);

Back

Step 3 of Parameterized SQL Commands

Front

create SQL connection object

Back

starts with T and has exactly three characters after

Front

LIKE 'T___'

Back

Step 1 of Parameterized SQL Commands

Front

create and set connection string variable of type string to retrieved connection string info from web.config file

Back

List products with order quantities greater than 100

Front

SELECT ProductName FROM Product WHERE Id IN (SELECT ProductId FROM OrderItem WHERE Quantity > 100)

Back

What would happen in this query SELECT c.customerid, c.companyName, orderid FROM customers c LEFT JOIN orders o ON o.customerid = c.customerid ORDER BY orderid

Front

Every customer name would be shown and an orderID, if one exists for that customer

Back

To generate scripts

Front

right click the database and click generate scripts, script entire database and all objects, choose Microsoft Azure SQL Database engine type, choose schema and data Types

Back

Use where to

Front

look for empty cells in a column, to look for values in a range including ends, to search for text withing a cell's value

Back

to make the primary key auto increment

Front

after declaring primary key in the create table statement add (1,1)

Back

Selects ID, NAME, AGE, ADDRESS, SALARY where there are 2 or more people who are the same age

Front

SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS GROUP BY age HAVING COUNT(age) >= 2;

Back

SELECT with TOP

Front

SELECT TOP 10 columnname FROM tablename ORDER BY columnname

Back

Inner Join

Front

SELECT columnname FROM tablename1 INNER JOIN tablename2 ON tablename1.PK=tablename2.PK

Back

To get the year

Front

YEAR (columnname)

Back

Select statement

Front

SELECT columnname FROM tablename

Back

subquery can be nested in

Front

SELECT, INSERT, UPDATE, or DELETE

Back

To get day

Front

DAY (columnname)

Back

To get month

Front

MONTH(columnname)

Back

Step 4 of Parameterized SQL Commands

Front

create SQLCommand object

Back

by default, order by

Front

sorts ascending

Back