Database Exam 2 SQL
1. Define what inner be a part of is
2. What are and describe the two varieties of join in standards's
3. What form of relationship is used to hyperlink tables together - ANS-1. A traditional be a part
of in which most effective rows that meet a given standards are decided on
2. -equijoin (or herbal be part of), if the join criteria contain equality
- theta be a part of, if the join criteria involve an inequality
3. Uses a PK-FK relationship
1. How does the CROSS JOIN method paintings?
2. Define Cartesian
3. What is the trouble with this form of be part of? - ANS-1.Provides the cartesian made of
tables, which leads to a unmarried desk of information
2. Cartesian product approach it'll take each product from desk one and integrate it with each
consecutive row on table , then it shows the columns we're choosing
three. The difficulty with CROSS JOIN, is that it consequences in inappropriate records
1. What is the entire range of rows which can be created with this JOIN
2. Create an example of ways a CROSS JOIN statement will look in combining different
columns of one-of-a-kind tables: - ANS-1. The quantity of rows in both tables extended together
2. SELECT item_desc, supplier_name FROM item, supplier
1.What is the LIKE clause used for?
2.What do the % and _ do within the LIKE clause - ANS-1. It offers the potential to do simple
pattern matching, in which patterns are usually expressed as a string
2. % fits zero or extra characters (* in Access)
_ matches exactly one person (# in Access)
Define the 2 facts kinds:
1. CHAR(n)
2. VARCHAR(n) - ANS-1. This allows you to specify a set period character
2. This is the extra efficient of the 2 to use as it permits it to store the quantity of facts that has
been surely used, and saves the greater space in case of alternate
Define the two statistics sorts:
1. Integer/SMALLINT
2. Decimal - ANS-1. This permits you to specify the quantity of reminiscence that should be
stored in keeping with value for numbers. SMALLINT being set to shop less numbers
2. (precession, scale)
, Define these three Primary string kinds
1. BLOB
2. TEXT
three. ENUM - ANS-1. Used for binary strings of statistics
2. Used for massive character strings of statistics
3. String item with a cost selected from a list of permitted values that is unique at table growing
time
Describe the IF declaration
Exercise: List all objects and their fees, together with a further column that identifies every item
as "pricey" if it expenses extra than $three, and "cheap" if it value much less than $3. Make sure
the object price has a $ signal with the aid of it, and it rounds to two decimal places. - ANS--
Allows for showing consequences that satisfy conflicting situations on the same time
- SELECT item_desc, CONCAT("$", FORMAT( item_price, 2), IF(item_price >= three, "pricey",
"cheap") As Category FROM object
Describe the UNION assertion and the two matters it helps
What are the two matters it calls for and the only component it deletes - ANS-- Combines the
output of two SELECT statements, it supports combining databases (new consumer info from
any other DB), and representing multuple competing situations simultaneously
- Requires the identical number of attributes in every assertion, corresponding records sorts to
be compatible, and gets rid of duplicates
Describe these four methods involved within the RLIKE clause:
1. "."
2. "t*"
3. "T+"
four. "t?" - ANS-1. Matches any single character
2. Matches zero or more times of the letter t
3. Matches one or extra times of the letter t
4. Matches zero or one instance of the letter t
Describe those four methods involved inside the RLIKE clause:
1. "^A"
2. "e$"
three. "[A-T]"
4. "[a-dx]" - ANS-1. Matches the given pattern at the start of a string
2. Matches the previous sample on the quit of the string
3. Matches more than a few characters
4. Matches any characters that are a, b, c, d, or x
1. Define what inner be a part of is
2. What are and describe the two varieties of join in standards's
3. What form of relationship is used to hyperlink tables together - ANS-1. A traditional be a part
of in which most effective rows that meet a given standards are decided on
2. -equijoin (or herbal be part of), if the join criteria contain equality
- theta be a part of, if the join criteria involve an inequality
3. Uses a PK-FK relationship
1. How does the CROSS JOIN method paintings?
2. Define Cartesian
3. What is the trouble with this form of be part of? - ANS-1.Provides the cartesian made of
tables, which leads to a unmarried desk of information
2. Cartesian product approach it'll take each product from desk one and integrate it with each
consecutive row on table , then it shows the columns we're choosing
three. The difficulty with CROSS JOIN, is that it consequences in inappropriate records
1. What is the entire range of rows which can be created with this JOIN
2. Create an example of ways a CROSS JOIN statement will look in combining different
columns of one-of-a-kind tables: - ANS-1. The quantity of rows in both tables extended together
2. SELECT item_desc, supplier_name FROM item, supplier
1.What is the LIKE clause used for?
2.What do the % and _ do within the LIKE clause - ANS-1. It offers the potential to do simple
pattern matching, in which patterns are usually expressed as a string
2. % fits zero or extra characters (* in Access)
_ matches exactly one person (# in Access)
Define the 2 facts kinds:
1. CHAR(n)
2. VARCHAR(n) - ANS-1. This allows you to specify a set period character
2. This is the extra efficient of the 2 to use as it permits it to store the quantity of facts that has
been surely used, and saves the greater space in case of alternate
Define the two statistics sorts:
1. Integer/SMALLINT
2. Decimal - ANS-1. This permits you to specify the quantity of reminiscence that should be
stored in keeping with value for numbers. SMALLINT being set to shop less numbers
2. (precession, scale)
, Define these three Primary string kinds
1. BLOB
2. TEXT
three. ENUM - ANS-1. Used for binary strings of statistics
2. Used for massive character strings of statistics
3. String item with a cost selected from a list of permitted values that is unique at table growing
time
Describe the IF declaration
Exercise: List all objects and their fees, together with a further column that identifies every item
as "pricey" if it expenses extra than $three, and "cheap" if it value much less than $3. Make sure
the object price has a $ signal with the aid of it, and it rounds to two decimal places. - ANS--
Allows for showing consequences that satisfy conflicting situations on the same time
- SELECT item_desc, CONCAT("$", FORMAT( item_price, 2), IF(item_price >= three, "pricey",
"cheap") As Category FROM object
Describe the UNION assertion and the two matters it helps
What are the two matters it calls for and the only component it deletes - ANS-- Combines the
output of two SELECT statements, it supports combining databases (new consumer info from
any other DB), and representing multuple competing situations simultaneously
- Requires the identical number of attributes in every assertion, corresponding records sorts to
be compatible, and gets rid of duplicates
Describe these four methods involved within the RLIKE clause:
1. "."
2. "t*"
3. "T+"
four. "t?" - ANS-1. Matches any single character
2. Matches zero or more times of the letter t
3. Matches one or extra times of the letter t
4. Matches zero or one instance of the letter t
Describe those four methods involved inside the RLIKE clause:
1. "^A"
2. "e$"
three. "[A-T]"
4. "[a-dx]" - ANS-1. Matches the given pattern at the start of a string
2. Matches the previous sample on the quit of the string
3. Matches more than a few characters
4. Matches any characters that are a, b, c, d, or x
