Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL DML > SQL Joins > Flashcards

SQL Joins Flashcards

1
Q

What is a SQL join?

A

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is an Inner Join?

A

Selects records that have matching values in both tables.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Write and Inner Join.

A

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is a Left Join?

A

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Write a Left Join.

A

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is a Right Join?

A

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Write a Right Join.

A

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is a Full Join?

A

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Write a Full Join.

A

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is a Self Join?

A

A self join is a regular join, but the table is joined with itself.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Write a Self Join.

A

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is a Union?

A

The UNION operator is used to combine the result-set of two or more SELECT statements.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Write a Union.

A

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is GroupBy?

A

The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Write a GroupBy.

A

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is the Having Clause used for?

A

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Write a Having Statement.

A

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is the OderBy Clause used for?

A

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Write an OrderBy statement.

A

SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

How do you How do you modify existing records in a table?

A

The UPDATE statement is used to modify the existing records in a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Write and update statement.

A

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

How do you put records in a table?

A

The INSERT INTO statement is used to insert new records in a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Write an Insert statement.

A

Write an Insert statement.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What is the IN operator?

A

The IN operator allows you to specify multiple values in a WHERE clause.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Write an IN statement.

A

WHERE Country IN (‘Germany’, ‘France’, ‘UK’);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What is the Between operator?

A

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Write a statement using Between.

A

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What is the Exists command used for?

A

The EXISTS operator is used to test for the existence of any record in a subquery.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Write an Exists statement.

A

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

How is Select Into used?

A

The SELECT INTO statement copies data from one table into a new table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

Write a Select Into statement..

A

SELECT * INTO CustomersBackup2017
FROM Customers;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

What is a Stored Procedure?

A

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

How do you create a Stored Procedure?

A

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

34
Q

What is a View?

A

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL statements and functions to a view and present the data as if the data were coming from one single table.

35
Q

How do you create a View?

A

CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = ‘Brazil’;

36
Q

How do you create a database?

A

The CREATE DATABASE statement is used to create a new SQL database.

37
Q

Write a sql statement that creates a database.

A

CREATE DATABASE testDB;

38
Q

How do you create a table in a database?

A

CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

39
Q

What is the Alter Table command used?

A

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

40
Q

Write a statement using Alter Table.

A

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

41
Q

What are Constraints in a database?

A

SQL constraints are used to specify rules for data in a table.

42
Q

What is the Unique Constraint?

A

The UNIQUE constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

43
Q

Use Unique Constraint in a statement.

A

CREATE TABLE Persons (
ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);

44
Q

What is a Primary Key?

A

The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

45
Q

Create a table with a Primary Key.

A

CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);

46
Q

What is a Foreign Key?

A

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

47
Q

Write a statement that creates a Foreign Key.

A

CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

48
Q

Wfhat is Auto Increment?

A

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

49
Q

What data type does SQL Server have?

A

String, numeric, and Date/Time.

50
Q

How do you debug a query????

A

Use SQL Server Profiler to see what queries are firing, how long they take, and what is being returned.

51
Q

What is a Dynamic variable?

A

A dynamic variable is a runtime variable whose type can change.

52
Q

fference between const and readonly?

A
53
Q

Difference between Shadowing and Overriding?

A
54
Q

Main tenets of OOP?

A

Abstraction

Encapsulation

Polymorphism

Inheritance

55
Q

What is Overriding?

A

The ability to change or augment the behavior of methods in classes, known as overriding their logic; it is one of the most powerful aspects of Object Oriented Programming.

56
Q

What is Overloading?

A

It is the ability to redefine a function of the same name using different signatures. The caller of the function can select the method they are calling by the different signature.

57
Q

How do you set up a .NET Core 3.x app for database access?

A

In the startup class a DbContext is setup in the ConfigureServices method,

58
Q

How is the Startup class used in .NET Core?

A

It sets up the services, middleware pipeline, and DI for the application.

59
Q

What are the HTTP verbs for a REST api?

A

GET - read

PUT - update/replace

POST - create

DELETE - delete

PATCH. PATCH - update/modify

60
Q

What is a Javascript Promise?

A

A promise is a mechanism for managing http calls in the browser.

61
Q

How do promises work?

A

You define a Promise that takes resolve and reject parameters. If the action of the function is resolved that branch is followed, etc.

To see the results of a Promise the “Then” method is called on the Promise. If the method fails the catch block is called.

62
Q

How do you write an arrow function?

A
63
Q

Describe an http Request.

A
64
Q

Describe an http Response.

A
65
Q

What is REST?

A

Representational State Transfer

It is a way of sending and recieving data across heterogenous networks using http.

66
Q

What does a well designed REST API look like?

A

Unlike traditional APIs that are code libraries you integrate into your code, the design of a REST API is primarily concerened with URIs. Each URI performs a specific function in the API, such as retrieving or posting data in some functional context. A well designed REST API in this context is concerned with the intuitiveness and the degree to which these URIs make sense for accomplishing a specific task.

67
Q

Talk about REST API Design.

A

API Design (URI)

Prefer nouns: /Customers, /Products, /Orders

Know what verbs will work on which URIs.

68
Q

REST API Design (cont).

A

Determine what query string will be used, if any.

69
Q

API Design (cont 2).

A

Know what each resource can do (verbs).

70
Q

What does Idempotent mean?

A

Idempotent

adj: an operation that can be executed multiple times without changing the result.

71
Q

Significance of Idempotency in REST?

A
72
Q

REST Idempotency example (bad api design)

A

You REST API design should make sure that if a PUT is done that changes an element that subsequent calls don’t fail. After that first change that succeeded, subsequent calls fail because the change had previously been made.

73
Q

REST API Design (cont 3).

A

Make sure you are not just returning everything from a datastore, but only the records you need.

  • potentially better performance
  • more intelligable for future developers
74
Q

Rest API (cont 4).

A
75
Q

REST API (cont 5).

A
76
Q

REST API (cont 6).

A
77
Q

REST API common formats.

A
78
Q

REST API Hypermedia (cont 7)

A

You can return hypermedia regarding your queries that provide additional information about your queries.

79
Q

REST API Design Lessons Learned.

A
80
Q
A
81
Q

What is a clustered index?

A

Clustered indexes are indexes whose order of the rows in the database correspond to the order of the rows in the index.

  • Clustered index modifies the way records are stored in a database based on the indexed column. Non-clustered index creates a separate entity within the table which references the original table.
  • Clustered index is used for easy and speedy retrieval of data from the database, whereas, fetching records from the non-clustered index is relatively slower.
  • In SQL, a table can have a single clustered index whereas it can have multiple non-clustered indexes.
82
Q
A

SQL DML (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions