Database/SQL
What is an RIGHT JOIN?
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
Define the ROLLBACK command in SQL.
ROLLBACK: This command restores the database to the last committed state. It is also used with the savepoint command to jump to a save point in a transaction.
Define the UPDATE command in SQL.
UPDATE: changes existing data in Table or View
Data tables can have hundreds, thousands, sometimes even millions of rows of data. These rows are often called ________.
records
What format is data within databases stored in?
Data within the most common types of databases in operation today is typically modeled in rows and columns in a series of tables to make processing and data querying efficient. The data can then be easily accessed, managed, modified, updated, controlled, and organized. Most databases use structured query language (SQL) for writing and querying data.
What is a DBA?
Database Administrator
Define the SAVEPOINT command in SQL.
SAVEPOINT: Savepoint command is used to temporarily save a transaction so that you can roll back to that point whenever necessary.
What is an INNER JOIN?
(INNER) JOIN: Returns records that have matching values in both tables (crosssection of two circles) This JOIN selects only those records from the database tables that have matching values. Returns rows when there is at least one match in both tables. Only matching rows from both source and target tables are retained
What are the benefits of SQL? (7)
1. Allows users to access data in the relational database management systems. 2. Allows users to describe the data. 3. Allows users to define the data in a database and manipulate that data. 4. Allows users to embed within other languages using SQL modules, libraries & pre-compilers. 5. Allows users to create and drop databases and tables. 6. Allows users to create, view, and store procedure functions in a database. 7. Allows users to set permissions on tables, procedures, and views.
What is a Non-Relational database? (and its main application used/other common name)
A Non-Relational database (also referred to as NoSQL, non-SQL, not only SQL or non-relational) is a high-performance, flexible type of database that does not use tables to store data. A NoSQL, or nonrelational database, allows unstructured and semi-structured data to be stored and manipulated (in contrast to a relational database, which defines how all data inserted into the database must be composed). NoSQL databases grew popular as web applications became more common and more complex.
What is a Relational Database? (and its main application used/another common name)
A Relational database (also referred to as SQL) is a type of database that allows us to identify and access data in relation to another piece of data in the database. Often, data in a relational database is organized into tables. Usually uses SQL Group of related spreadsheets Retrieves information through Queries
What is a Database?
A database is a collection of information that is organized so that it can be easily accessed, managed, and updated. A database is an organized collection of structured information, or data, typically stored electronically in a computer system. They allow us to store, query, filter and manage large amounts of data. Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just a database.
What is a database usually controlled by?
A database is usually controlled by a database management system (DBMS) or otherwise called a database SERVER. They handle the storage, retrieval, and updating of data. Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just a database.
How do you create a foreign key?
A foreign key is an attribute in one table whose value must either match the primary key in another table or be NULL. Consider the following two tables, "Customers" and "Orders" CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); CREATE TABLE ORDERS ( ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT references CUSTOMERS(ID), AMOUNT double, PRIMARY KEY (ID) ); Once the tables have been created, the foreign key can be created using the following syntax : ALTER TABLE ORDERS ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);
What is a Primary Key in MySQL?
A primary key is a field in a table that uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields. A primary key also called a primary keyword, is a key in a relational database that is unique for each record. It is a unique identifier, such as a driver's license number, telephone number (including area code), or vehicle identification number (VIN). In MySQL, a primary key is a single field or combination of fields that uniquely defines a record. - None of the fields that are part of the primary key can contain a NULL value. - A relational database table must always have one and only one primary key of each value assignment.
Define the DROP command in SQL.
DROP: used to remove existing entities; delete objects from database
What is RDBMS?
A relational database management system (RDBMS) is a program that allows you to create, update, and administer a relational database. Most relational database management systems use the SQL language to access the database. Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.
Define the ALTER command in SQL.
ALTER: used to modify the definition of an existing object or alters the structure of the database
Define the COMMIT command in SQL.
COMMIT: Commit command is used to permanently save any transaction into the database.
Define the CREATE command in SQL.
CREATE: used to define (or create) new objects
What are DCL SQL Statements? (and name 2 commands)
DCL - Data Control Language DCL statements are used to control the access of data stored in a database and provide data security. GRANT, REVOKE
What are the 4 types of SQL Statements?
DDL DML DCL TCL
What are DDL SQL Statements? (and name 3 commands)
DDL - Data Definition Language Statements used to CREATE, ALTER, or DROP database objects. CREATE, ALTER, DROP
Define the DELETE command in SQL.
DELETE: removes one or more rows from a Table or View
What are DML SQL Statements? (and name 5 commands)
DML - Data Manipulation Language Statements used to SELECT (retrieve), INSERT, UPDATE, DELETE (remove) data within DB objects. DML statements are used for managing data with schema objects. SELECT, INSERT, UPDATE, DELETE, WHERE
What is a Database Server?
Database servers consist of database software that's running that your able to read and write from. - MySQL Workbench (Dolphin symbol) is a database server (also called a DBMS or database system) - The syntax or language used in servers that control databases is usually SQL.
What is an FULL JOIN?
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
Define the GRANT command in SQL.
GRANT: grants permissions on a securable to a user
How do you create a primary key?
Generally, the primary key is created while creating the database and the table. The primary key can also be created after the creation of the table as shown below. CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);
What are the different types of SQL Joins?
INNER JOIN LEFT JOIN RIGHT JOIN FULL JOIN
Define the INSERT command in SQL.
INSERT: insert one or more rows from a Table or View The SQL INSERT INTO Statement is used to add new rows of data to a table in the database. Syntax : There are two basic syntax's of the INSERT INTO statement which are shown below. INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
What is an LEFT JOIN?
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
What is the biggest DBMS Example of NoSQL?
MongoDB
What is MongoDB?
MongoDB (link resides outside IBM) is an open-source, non-relational database management system (DBMS) that uses flexible documents instead of tables and rows to process and store various forms of data. MongoDB is an open-source, NoSQL database that provides support for JSON-styled, document-oriented storage systems. Its flexible data model enables you to store data of any structure. It is an open-source, document-oriented DBMS A non-relational, schema-less store of JSON documents
What is MySQL?
MySQL is a relational database management system (RDBMS) developed by Oracle that is based on structured query language (SQL). - MySQL is an RDBMS to store, retrieve, modify and administrate a database. - MYSQL is database server software. - MySQL is an RDBMS that allows data to be kept organized in an existing database. - MySQL RDBMS uses the SQL language to query the database. Or to access, update, or manipulate the data in the database.
What are the 2 main categories of Databases? (and their main application used)
Relational - SQL Non-Relational - NoSQL
How do NoSQL Databases store data?
NoSQL databases use a variety of data models, including document, graph, key/value, and columnar. Can be used to store image meta-data for example. This is a good use for NoSQL because image data doesn't necessarily require a lot of structure and can be stored as a "key" & "value" pair.
What is the difference between RDBMS and SQL?
RDBMS is a relational database management system. And SQL is the language used for communicating with data in an RDBMS. Or in plain terms, RDBMS is a book and SQL is the language being used in the book.
Define the REVOKE command in SQL.
REVOKE: removes a previously granted or denied permission
Define the SELECT command in SQL.
SELECT: retrieves one or more rows from a Table or View The SQL SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result-sets. SELECT column1, column2, columnN FROM table_name; If you want to fetch all the fields available in the field, then you can use the following syntax. SELECT * FROM table_name;
What is SQL? (and what type of database uses it)
SQL (Structured Query Language) is a programming language used to communicate with data stored in a relational database management system. SQL syntax is similar to the English language, which makes it relatively easy to write, read, and interpret. Structured Query Language (SQL) is used to create and use databases, tables, and relationships. A language that makes it possible to easily manage the requested data. SQL is divided into two categories: SQL statements for database definition and SQL statements for database processing (querying and updating). The database definition commands are referred to as a data definition language (DDL), and the database query and update commands are referred to as a data manipulation language (DML).
What path does an SQL Query take to access the physical database? (4 levels) (SQL Architecture)
SQL Query --> Query Language Processor --> DBMS Engine --> Physical Database
What is the difference between SQL and mySQL?
SQL is a standard language USED for retrieving and manipulating structured databases. MySQL IS the relational database management system. - SQL is a structured query language that is used for manipulating and accessing the relational database. - MySQL itself is a relational database that uses SQL as the standard database language. - MySQL RDBMS uses the SQL language to query the database. Or to access, update, or manipulate the data in the database. - SQL is the language used for operating, accessing, updating, and manipulating data in a database. - MySQL is an RDBMS that allows data to be kept organized in an existing database. - SQL is a Structured Query Language. - MySQL is an RDBMS to store, retrieve, modify and administrate a database. - SQL is a query language. - MYSQL is database software. - SQL is a language which is used to operate your database. SQL is the basic language used for all the databases. SQL is used in the accessing, updating, and manipulation of data IN a database. Its design allows for the management of data in an RDBMS, such as MySQL. The SQL language is also used for controlling data access and for the creation and modification of database schemas.
What is the SQL ANSI standard?
SQL was first developed at IBM in the 1970s with Oracle as a major contributor, which led to the implementation of the SQL ANSI standard, SQL has spurred many extensions from companies such as IBM, Oracle, and Microsoft. Although SQL is still widely used today, new programming languages are beginning to appear. SQL was developed by IBM and is endorsed as a national standard by the American National Standards Institute (ANSI). Although a newer standard, SQL3, exists, the most widely implemented version of SQL is the ANSI SQL-92 standard. SQL is not a full-featured programming language, but rather it is considered to be a data sublanguage.
What does SQL stand for?
Structured Query Language
What are TCL SQL Statements? (and name 3 commands)
TCL - Transaction Control Language Transaction Control Language commands are used to manage transactions in the database. These are used to manage the changes made by DML statements. It also allows statements to be grouped together into logical transactions. SAVEPOINT, ROLLBACK, COMMIT
Define the JOIN command in SQL.
The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. Let us join these two tables with a SELECT Query : SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
Define the WHERE command in SQL.
The SQL WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. You should use the WHERE clause to filter the records and fetching only the necessary records. The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement. SELECT column1, column2, columnN FROM table_name WHERE [condition] Using the same "Customers" table as before and one would like to retrieve all the names of customers who's age is greater than 20, the following query can be used. SELECT CUSTOMERS.AGE from CUSTOMERS where AGE > 20; // Can use DB name as prefix before column name.
What is a Foreign Key?
This is a column in one table that points to the primary key in another table. A FOREIGN KEY in one table points to a PRIMARY KEY in another table and acts to provide a logical relationship between the two tables. - A foreign key is a key used to link two tables together. This is sometimes also called a referencing key. - A Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table. - The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.
Data tables can also have many columns of data. Columns are labeled with a descriptive name (say, age for example) and have a specific _____ _____.
data type