- 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?
- What is the need for group functions in SQL?
- What is a Relationship and what are they?
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