Finals Reviewer pt1 Flashcards
Includes commands to create database objects such as tables, indexes, and views, as well as commands to define access rights to those database
Data Definition Language
is a group of database objects such as tables and indexes that are related to each other
Schema
is a collection of related data held in a table format within a database
Table
Constraint wherein a column or columns are used to establish and enforce a link between the data in two tables
Foreign key
Constraint that ensures that no duplicate values are entered in specific columns that do not participate in a primary key
Unique
SQL command used to remove an object such as an index, column or even a table from a database permanently
DROP
command is used to list the contents of a table
SELECT
A special operator that we use if we want to check whether an attribute value is within a range
BETWEEN
is a function that returns the current system date for MySQL
NOW()
A special operator that we use to check whether a subquery returns any rows
EXISTS
A special operator that we use to check whether an attribute value matches any value within a value list
IN
A special operator that we use to check whether an attribute value matches a given string
LIKE
A like special operator used in MySQL that means any single character NOT within a range or a set
[^]
All changes in the table structure are made by using table command
ALTER TABLE
MySQL uses the column command to change a column attribute
ALTER
this type of subquery can be used anywhere a table is expected, such as when using the “FROM” clause
Computed Columns
An SQL Server function that produces a date by adding a specified number to a specified part of date
DATEADD
A MySQL function that returns the time between two dates
DATEDIFF
An SQL Server function that returns the specified part of the date requested
DATEPART
A MySQL function that is used to return a single part of a date/time, such as year, month, day, hour, minutes, etc.
EXTRACT()
An SQL Server function that will extract the year from a value stored as a SMALLDATETIME data type
YEAR()
An SQL Server function that returns the current system date and time
GETDATE()
The function rounds numbers to a specified number of decimal places
ROUND
The ??? function returns the next lower integer value when a number contains decimal places
FLOOR
The function returns the absolute value of any numeric value
ABS
An SQL Server function that returns a value if a table value is null
ISNULL
A function that returns a NULL if expression1 = expression2. If the expressions are not equal, then expression is returned
NULLIF
An SQL Server function that is used to display or return from a result set the rows that fall at the TOP of a range specified by an ORDER BY clause
TOP
A MySQL specialized conversion function that formats the number X to a format like ‘#,###,#####’, rounded to D decimal places, and returns the result as a string
FORMAT()
An SQL Server specialized conversion function that is also used to explicitly convert to a given data type
STR
A string function that returns part of a string
SUBSTRING
An MySQL string function that returns the length (number of characters) of a desired string excluding trailing blanks.
LEN
An SQL Server string function that returns the string in UPPERCASE
UPPER
A MySQL string function that returns the starting position of a specified pattern.
INSTR
An SQL Server string function that returns the starting position of a specified pattern.
CHARINDEX ()
A string function that removes blanks from the end (right) of a string
RTRIM
A MySQL function that tests whether an expression is NULL
ISNULL
An SQL Server function returns a certain percentage of rows that falls at the top of a specified range
PERCENT
A MySQL clause that is used in the SELECT statement to constrain the number of rows in a result set
LIMIT
A part of the MySQL LIMIT clause that specifies maximum number of rows to return
count
An SQL Server specialized conversion function that always converts from a number (for example, float or numeric) to a character data type.
STR
An SQL Server string function that returns the length (number of characters) of a desired string excluding trailing blanks
LEN
An SQL Server string function that returns the string in LOWERCASE.
LOWER
A MySQL string function that returns the substring from the given string before a specified number of occurrences of a delimiter
SUBSTRING_INDEX()
A string function that starts from the right of the string expression or column and returns n characters
RIGHT
A string function that returns part of a string.
SUBSTRING
in SQL Server, the ??? table contains all the values we updated using the UPDATE command
UPDATE
IDK if SELECT or UPDATE yung tamang sagot
in SQL Server, the table contains all the values we deleted using the DELETE command.
DELETE
to delete or remove a trigger we use _trigger_name command
DROP TRIGGER
in MySQL, the ??? table contains all the values we deleted using the DELETE command
OLD
