SQL Joins

Question 1

What is a SQL join?

Answer 1

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

Question 2

What is an Inner Join?

Answer 2

Selects records that have matching values in both tables.

Question 3

Write and Inner Join.

Answer 3

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

Question 4

What is a Left Join?

Answer 4

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.

Question 5

Write a Left Join.

Answer 5

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

Question 6

What is a Right Join?

Answer 6

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.

Question 7

Write a Right Join.

Answer 7

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.

Question 8

What is a Full Join?

Answer 8

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

Question 9

Write a Full Join.

Answer 9

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

Question 10

What is a Self Join?

Answer 10

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

Question 11

Write a Self Join.

Answer 11

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

Question 12

What is a Union?

Answer 12

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

Question 13

Write a Union.

Answer 13

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

Question 14

What is GroupBy?

Answer 14

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.

Question 15

Write a GroupBy.

Answer 15

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

Question 16

What is the Having Clause used for?

Answer 16

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

Question 17

Write a Having Statement.

Answer 17

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

Question 18

What is the OderBy Clause used for?

Answer 18

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

Question 19

Write an OrderBy statement.

Answer 19

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

Question 20

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

Answer 20

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

Question 21

Write and update statement.

Answer 21

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

Question 22

How do you put records in a table?

Answer 22

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

Question 23

Write an Insert statement.

Answer 23

Write an Insert statement.

Question 24

What is the IN operator?

Answer 24

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

Question 25

Write an IN statement.

Answer 25

WHERE Country IN ('Germany', 'France', 'UK');

Question 26

What is the Between operator?

Answer 26

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

Question 27

Write a statement using Between.

Answer 27

SELECT \* FROM Products WHERE Price BETWEEN 10 AND 20;

Question 28

What is the Exists command used for?

Answer 28

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

Question 29

Write an Exists statement.

Answer 29

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

Question 30

How is Select Into used?

Answer 30

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

Question 31

Write a Select Into statement..

Answer 31

SELECT \* INTO CustomersBackup2017 FROM Customers;

Question 32

What is a Stored Procedure?

Answer 32

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.

Question 33

How do you create a Stored Procedure?

Answer 33

CREATE PROCEDURE SelectAllCustomers AS SELECT \* FROM Customers GO;

Question 34

What is a View?

Answer 34

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.

Question 35

How do you create a View?

Answer 35

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

Question 36

How do you create a database?

Answer 36

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

Question 37

Write a sql statement that creates a database.

Answer 37

CREATE DATABASE testDB;

Question 38

How do you create a table in a database?

Answer 38

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

Question 39

What is the Alter Table command used?

Answer 39

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

Question 40

Write a statement using Alter Table.

Answer 40

ALTER TABLE table\_name ALTER COLUMN column\_name datatype;

Question 41

What are Constraints in a database?

Answer 41

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

Question 42

What is the Unique Constraint?

Answer 42

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.

Question 43

Use Unique Constraint in a statement.

Answer 43

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

Question 44

What is a Primary Key?

Answer 44

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).

Question 45

Create a table with a Primary Key.

Answer 45

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

Question 46

What is a Foreign Key?

Answer 46

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.

Question 47

Write a statement that creates a Foreign Key.

Answer 47

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);

Question 48

Wfhat is Auto Increment?

Answer 48

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.

Question 49

What data type does SQL Server have?

Answer 49

String, numeric, and Date/Time.

Question 50

How do you debug a query????

Answer 50

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

Question 51

What is a Dynamic variable?

Answer 51

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

Question 52

fference between const and readonly?

Question 53

Difference between Shadowing and Overriding?

Answer 53

Question 54

Main tenets of OOP?

Answer 54

Abstraction Encapsulation Polymorphism Inheritance

Question 55

What is Overriding?

Answer 55

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.

Question 56

What is Overloading?

Answer 56

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.

Question 57

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

Answer 57

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

Question 58

How is the Startup class used in .NET Core?

Answer 58

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

Question 59

What are the HTTP verbs for a REST api?

Answer 59

GET - read PUT - update/replace POST - create DELETE - delete PATCH. PATCH - update/modify

Question 60

What is a Javascript Promise?

Answer 60

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

Question 61

How do promises work?

Answer 61

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.

Question 62

How do you write an arrow function?

Answer 62

Question 63

Describe an http Request.

Answer 63

Question 64

Describe an http Response.

Answer 64

Question 65

What is REST?

Answer 65

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

Question 66

What does a well designed REST API look like?

Answer 66

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.

Question 67

Talk about REST API Design.

Answer 67

API Design (URI) Prefer nouns: /Customers, /Products, /Orders Know what verbs will work on which URIs.

Question 68

REST API Design (cont).

Answer 68

Determine what query string will be used, if any.

Question 69

API Design (cont 2).

Answer 69

Know what each resource can do (verbs).

Question 70

What does Idempotent mean?

Answer 70

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

Question 71

Significance of Idempotency in REST?

Answer 71

Question 72

REST Idempotency example (bad api design)

Answer 72

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.

Question 73

REST API Design (cont 3).

Answer 73

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

Question 74

Rest API (cont 4).

Answer 74

Question 75

REST API (cont 5).

Answer 75

Question 76

REST API (cont 6).

Answer 76

Question 77

REST API common formats.

Answer 77

Question 78

REST API Hypermedia (cont 7)

Answer 78

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

Question 79

REST API Design Lessons Learned.

Answer 79

Question 81

What is a clustered index?

Answer 81

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.