SQL Entry Level Terms

What are the different types of SQL join statements?

1) INNER JOIN 2) LEFT JOIN 3) RIGHT JOIN 4) FULL JOIN

Client

A single-user computer that interfaces with the multiple-user server.

Is a NULL value same as zero or a blank space? If not then what is the difference?

A NULL value is not same as zero or a blank space. A NULL value is a value which is 'unavailable, unassigned, unknown or not applicable'. Whereas, zero is a number and blank space is a character.

What is an SQL Join Statement, generally speaking.

A SQL Join statement is used to combine data or rows from two or more tables based on a common field between them.

LIKE operator

A character operator. When used in conjunction with asterisks or brackets, matches parts of a value within a table.

Database

A collection of electronically stored organized files.

NOT operator

A logical operator used to match any condition opposite of the one defined.

IN operator

A miscellaneous operator that provides a shorter method for specifying a range of conditions.

Server

A multiple-user computer that provides shared database connection, interfacing, and processing services.

If a table contains duplicate rows, does a query result display the duplicate values by default? How can you eliminate duplicate rows from a query result?

A query result displays all rows including the duplicate rows. To eliminate duplicate rows in the result, the DISTINCT keyword is used in the SELECT clause.

Query

A question or command you pose concerning data from the database.

Row

A record that represents a collection of information relating to an item in a table.

Parts of a query include the following: Clause

A segment of an SQL statement combined to form a complete SQL statement.

Client/server database system

A system that divides processing between client computers and a database server.

Normalization

A three-step technique used to organize data attributes in a more efficient, reliable, flexible, and maintainable structure.

Say True or False. Give explanation if False. By default the group functions consider only distinct values in the set.

By default, group functions consider all values including the duplicate values.

What is a FULL JOIN?

FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values.

Field

Column that runs vertically within a table.

What does CRUD stand for?

Create, Read, Update, and Delete

What is the purpose of the group functions in SQL? Give some examples of group functions.

Group functions in SQL work on sets of rows and returns one result per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.

How do you search for a value in a database table when you don't have the exact value to search for?

In such cases, the LIKE condition operator is used to select rows that match a character pattern. This is also called 'wildcard' search. LIKE is used in the WHERE clause.

What is the difference between BETWEEN and IN operators in SQL? BETWEEN

The BETWEEN operator is used to fetch rows based on a range of values. The IN operator is used to check for values contained in specific sets.

Parts of a query include the following: Statement

Keywords and data supplied within an SQL query.

What are the case manipulation functions of SQL?

LOWER, UPPER, INITCAP

Foreign key

Link records of one type with those of another type. A foreign key is a primary key in another table. It's used to reduce redundant data.

Arithmetic Operators

Plus (+) Minus (-) Divide (/) Modulus (%) Multiply (*)

What is a RIGHT JOIN (aka. RIGHT OUTER JOIN)

RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.

Parts of a query include the following: Keywords

Reserved words that allow you to communicate with a database.

ORDER BY clause

Retrieves the name of one or more columns from the SELECT statement, and sorts (ascending or descending) the output.

What are the specific uses of SQL functions?

SQL functions have the following uses − Performing calculations on data Modifying individual data items Manipulating the output Formatting dates and numbers Converting data types

SQL Definition

SQL is a nonprocedural language that is used to manipulate and retrieve data from relational DBMSs such as Microsoft Access, DB2, Sybase, and Microsoft SQL Server. It is considered nonprocedural because of the way operations are carried out. Unlike procedural computer languages like Basic and C, which are concerned with how to perform operations, SQL describes what needs to be processed. The focus is on what to retrieve, delete, or insert.

What is the difference between SQL and MySQL or SQL Server?

SQL or Structured Query Language is a language; language that communicates with a relational database thus providing ways of manipulating and creating databases. MySQL and Microsoft's SQL Server both are relational database management systems that use SQL as their standard relational database language.

Data type

Specifies the type of data that a column can hold (e.g., text and numbers).

Stored Procedures (SQL)

Statements with an assigned name, which are stored in a relational database management system as a group, so it can be reused and shared by multiple programs.

Table

Structured file containing rows and columns.

Syntax Rules

Syntax refers to the rules that govern how a programming language must be written.

What is the purpose of the condition operators BETWEEN and IN?

The BETWEEN operator displays rows based on a range of values. The IN condition operator checks for values contained in a specific set of values.

What is an INNER JOIN?

The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be same.

Sorting Data Using the WHERE Clause

The ability to sort data further enhances the data-retrieval process. Sorting data involves filtering and arranging retrieved data in a customized fashion. In SQL, this is achieved by using the following clauses: GROUP BY HAVING ORDER BY STARTING WITH WHERE

Comparison Operators

The comparison operators are used to compare expressions. An expression is any data type that returns a value

What is the default ordering of data using the ORDER BY clause? How could it be changed?

The default sorting order is ascending. It can be changed using the DESC keyword, after the column name in the ORDER BY clause.

What is a LEFT JOIN (aka. LEFT OUTER JOIN)?

This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join. The rows for which there is no matching row on right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER

Primary Key

Uniquely identify a row or record in a table. It's typically an ID column that consists of an auto incrementing integer.

ASC keyword

Used to sort data in ascending order.

DESC keyword

Used to sort data in descending order

Keywords: SELECT keyword

Used to tell the database which column(s) to display.

Keywords: FROM keyword

Used to tell the database which table to retrieve the columns from.

Asterisk wildcard character (*)

When used with the LIKE operator, you can match any number of occurrences of any character.

Brackets wildcard character ( [] )

When used with the LIKE operator, you can specify a set of characters that match a character in a specific position.

Can you sort a column using a column alias?

Yes. A column alias could be used in the ORDER BY clause.