Please discuss these questions with your partners and post your answers as a comment on this gist when you are finished.
Team: Hayder, Hakeema, Ramah, Osama
Q0: The difference is SQL is a programming language which MySQL, which is a open-sourced software, uses. SQL is mainly used for storing, manipulating and retrieving data in a RDB, while MySQL is a open source DB management system, That allows managing relational databases,
Q1: DBMS is Database management system software, which is a computer program designed to manage a large amount of structured data, and run operations on the desired data requested by the users. There are different types of DBMS, some of them are: Relation Databases, Object Oriented Databases, Hierarchical Databases, Network Databases.
Q2: The different types of joins are: INNER JOIN, OUTER JOIN, CROSS JOIN, SELF JOIN. Examples of them:
INNER JOIN: Returns only the rows where there is a match in both tables.
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
OUTER JOIN: Returns all rows from the left table (For this example) and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
CROSS JOIN: is used to generate the Cartesian product of two tables, creating all possible combinations of rows between them without any specific matching condition.
SELECT students.student_id, students.student_name, subjects.subject_id, subjects.subject_name
FROM students
CROSS JOIN subjects;
SELF JOIN: A self join is used to combine rows within a table, treating it as if it were two separate tables.
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
Q3: A primary key, is a column in a relational database table that's distinctive for each record. It's a unique identifier. A relational database must have only one primary key. Every row of data must have a primary key value and none of the rows can be null.
Q4: Some of the SQL operators are: Arithmetic Operators, Bitwise Operators, Comparison Operators, Compound Operators, Logical Operators.
Q5: Very basically, the GROUPING function in SQL helps in dealing with complex queries.
Q6: a relationship refers to the association between tables in a database. And they are: One-to-Many, Many-to-Many, One-to-One
Examples of each:
One-to-Many: Customer that has many Order
One-to-One: Every person has only one ID, or passport
Many-to-Many: Many Products that have Many Customers
**### Team Group:
Lubna Abdelkhaliq
Nour Kayyali
Wajd Al-Kayyali
Ammar Kolko
SQL is a query programming language that manages RDBMS.
MySQL is a relational database management system that uses SQL.
SQL is primarily used to query and operate database systems.
MySQL allows you to handle, store, modify and delete data and store data in an organized way
A database management system (DBMS) is a computer program designed to manage a large amount of structured data, and run operations on the desired data requested by the users
There are three main types of DBMS data models: relational, network, and hierarchical.
Relational data model: Data is organized as logically independent tables.
Network data model: All entities are organized in graphical representations.
Hierarchical data model: Data is organized into a tree-like structure.
Purpose: Retrieves records that have matching values in both tables.
Example: SELECT customers.customer_id, orders.order_date
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;
Result: Returns only customers who have placed orders, with their corresponding order dates.
Purpose: Retrieves all records from the left table, and the matched records from the right table. If there's no match in the right table, NULL values are returned for those columns.
Example :SELECT customers.customer_id, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;
Result: Returns all customers, including those without orders (showing NULL for order_date).
3)Right Outer Join:
Purpose: Retrieves all records from the right table, and the matched records from the left table. If there's no match in the left table, NULL values are returned for those columns.
Example :SELECT customers.customer_id, orders.order_date
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;
Result: Returns all orders, including those without a corresponding customer (showing NULL for customer_id).
Purpose: Retrieves all records from both tables, including unmatched rows, filling in NULL values where there's no match.
Example :SELECT customers.customer_id, orders.order_date
FROM customers
FULL JOIN orders
ON customers.customer_id = orders.customer_id;
Result: Returns all customers and all orders, even if they don't have a corresponding match in the other table.
A primary key is a special column or set of columns in a database table that uniquely identifies each row in that table
***key characteristics of primary keys:
-Uniqueness: Each value in the primary key column(s) must be unique within the table. This means no two rows can have the same primary key value.
-Not Null: Primary key columns cannot contain null values. They must always have a value to ensure they can uniquely identify each row.
-Single Primary Key per Table: A table can have only one primary key.
***Purpose of Primary Keys:
Preventing Duplicates, Creating Relationships
Logical // Compound // Comparison // Bitwise // Arithmetic https://www.w3schools.com/sql/sql_operators.asp
In SQL, the GROUPING function is a very useful tool when dealing with complex queries that involve grouping sets, rollup, and cube operations. Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT
In SQL, a relationship refers to a connection between two tables based on a common column. It is established using foreign keys, which link the data in one table to the data in another. This helps maintain data integrity and enables queries that involve data from multiple tables. There are different types of relationships, such as one-to-one, one-to-many, and many-to-many, each serving specific data modeling needs.
SQL serves as a query programming language designed for managing Relational Database Management Systems (RDBMS). MySQL, in turn, is an RDBMS that employs SQL for its operations. The primary function of SQL revolves around querying and manipulating database systems. MySQL, leveraging SQL, facilitates the handling, storage, modification, and deletion of data, ensuring organized data storage.
A Database Management System (DBMS) is essentially a computerized system for maintaining and organizing data. Users of the system are provided with tools to perform various operations, allowing for the manipulation of data within the database or the management of the database structure. DBMSs are classified based on their data structures or types.
A. Object-Oriented Database:
B. Hierarchical Database:
C. Network Database:
A primary key in SQL refers to either a single field or a group of fields or columns within a table that serves the crucial function of uniquely identifying each row. In essence, it is a designated column that exclusively accepts distinct values for every individual row.
A. Arithmetic SQL Operators:
B. Comparison SQL Operators:
C. Logical SQL Operators:
Group functions in SQL, particularly the GROUPING function, play a crucial role in managing complex queries involving grouping sets, rollup, and cube operations. This function helps differentiate between NULLs representing the absence of data and those present in super-aggregate results. By returning 1 for super-aggregate rows and 0 for regular grouped rows, the GROUPING function aids in clarifying results. Whether used in the SELECT statement, HAVING clause, or ORDER BY clause, this function requires a column name as an argument and must be paired with a GROUP BY clause. Its significance lies in distinguishing NULLs within super-aggregate rows, contributing to clearer and more comprehensible query outcomes.
A relationship refers to the established connections between two or more tables, typically based on shared fields. These connections often involve primary and foreign keys.
Abdullah Alawad / Hassan AbuGhareeb / hammam AbuShehadeh / Noor Alrai**
SQL: Structured Query is a query programming language that manages RDBMS.
MySQL is a relational database management system that uses SQL.
DBMS : Data Base management Systems : system that is used to store and mange data
Types: -Relational -Network DBMS -Object Oriented DB
(INNER) JOIN : Returns records that have matching values in both tables.
returns a result that includes rows from both left and right tables.
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
You can imagine it with a Venn diagram with two circles, with the resulting table being the green highlighted part which includes
both the common/overlapping part, and the rest of the left circle.
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
The RIGHT OUTER JOIN selects data from the right table (Table B) and matches this data with the rows from the left table
(Table A). The RIGHT JOIN returns a result set that includes all rows in the right table
Self Join: join a table to itself, can be viewed as a join of two copies of the same table.
Cross Join (also known as a cartesian join): returns all combinations of rows from each table.
CROSS JOIN joins every row from the first table with every row from the second table and its result comprises all combinations of records
in two tables.
PK: is a field that uniquely identify each record in a table
Arithmetic / Comparison / Bitwise / Logical / Compound
COUNT(): Counts the number of rows in a set.
SUM(): Calculates the sum of numeric values in a set.
AVG(): Computes the average of numeric values in a set.
MIN(): Finds the minimum value in a set.
MAX(): Finds the maximum value in a set.
The need is to summarize Data in one value
Relations the established associations between two or more tables
One to One:
One to Many
Many to Many
Gorgees, Dana
What is the difference between SQL and MySQL?
What do you mean by DBMS? What are its different types?
What are the types of joins in SQL? Give an example for each one:
What is a Primary key?
What are the different operators available in SQL?
Q1 SQL is a query language, MySQL is a relational database that uses SQL to query a database
Q2 DBMS Database Management Systems. It is a software system that allows users to store, modify, and extract data from a database.
Types of DBMS:
1-Relational database.
2-Object oriented database.
3-Hierarchical database.
4-Network database.
Q3 A primary key is a field in a database table that uniquely identifies each record in the table.
Q4
Types of SQL operators:
1- comparison operators ( =, <, >, <=, >=, <>)
2- logical operators (AND, OR, NOT)
3- arithmetic operators (+, -, *, /)
4- string operators (LIKE, IN, BETWEEN)
Q5 SQL are used to perform calculations
Q6
Tables Relations in SQL Server:
One-to-One,
One-to-Many,
Many-to-Many,