Exam Flashcards
includes commands to create database
Data Definition Language
is a group of database objects
Schema
is a collection of related data
Table
type of constraint. . to extablish and enforce a link
Foreign Key
type of constraints that ensures. . no duplicate values
Unique Constraint / Unique
what sql command is used to remove an object
DROP
command is used to list the contents of a table
SELECT
is a function that returns the current system date for MySQL
CURDATE()
wheter an attribute value is within a range
BETWEEN
subquery returns any rows
EXISTS
the highest maximmum value in the table
MAX()
the toal sum for any specified attribute
SUM()
when you have attribute columns combined with aggregate functions
GROUP BY
condition is generally composed of an equality comparison between
Referential Integrity Constraint (RIC)
type of join wherein a table is connected to itself
Self join
type of outer join wherein it will show all rows
Right outer join
statement combines rows from two or more queries
UNION
join performs a relational product
Cartesian Join
this select subquery is used anywhere a single value is expected
Scalar subquery
operator allows you to check if a single value exists
IN operator
one way of reading every row in a table and comparing values in each row against related data
Correlated subquery
type of subquery runs once for each row in the outer query, using values
DATE_ADD()
MySQL funcion adds a specified interval
DATEDIFF()
SQL server function calculates the difference between two dates
DATE_SUB()
SQL server function extracts the month part
MONTH()
SQL function returns the current UTC date
GETUTCDATE()
SQL supports carious arithmetic functions for calculations
Arithmetic functions
function rounds a number up to the nearest integer
CEILING()
SQL server function proivdes a alternative value
ISNULL()
SQL Server: Up to _ _ _ characters (depending on the version)
MySQL: Up to _ _ characters
SQL Server: Up to 128 characters (depending on the version)
MySQL: Up to 64 characters
false - 2,200 input and output
true - 128MB
we can use execute
EXEC
the _____ clause is generally used when you have attribute columns combined with aggregate function SELECT statement
having
A _____ is a virtual table based on a SELECT query
view
or cursor??
____ condition is generally composed of an equality comparison between the foreign key and the primary key of relate tables
join
The ____ statement combines rows from two or more queries
union
A _____ join performs a relational product
cross
a type of outer join wherein it will show all rows from the right table regardless if there’s a match on the left table
right join
this select subquery is used anywhere a single value is expected
scalar subquery
When you want to compare a single attribute to a list of values, you use ____ operator
IN
____ subquery is a subquery that executes once for each row in the outer query
correlated
MySQL function that substracts a specified time interval from a date
DATE_SUB()
SQL server function that returns the current system date and time in UTC format
GETUTCDATE()
clause is used to restrict the groups that will be included in a query result?
Having
a ____ “function” can be used to perform an arithmetic operation on a column
+ / Add
Which operator finds rows that do not contain a null value in the specified column?
IS NOT NULL
Which operator finds rows that do not contain a null value in the specified column?
LIKE
Which function determines the number of rows in a table?
COUNT
the _____ function returns the next larger integer value when a number contains decimal places
round
An SQL Server function that returns a value if a table value is null
IS NULL
A _____ parameter is part of a stored procedure that allows it to return a value or multiple values
output
How many characers can a stored procedure name contain?
128
