- 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.
Sakhile, Konani, Simphiwe (LEADER)
SQL (Structured query language) - A language used to handle databases. This language is used to update and manage data on databases. MySQL - is basically an open-source management system that uses SQL as an agent to manage the data on databases. to simplify it, it is a data management system.
A database management system is software that uses a standard method to store and organize data. it acts as an interface between the end-users and the database.
In SQL, there are several types of joins used to combine rows from two or more tables based on related columns.
E.g:
INNER JOIN: Returns records that have matching values in both tables.
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and the matched records from the right table. The result is NULL from the right side if there is no match.
SELECT orders.order_id, customers.customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and the matched records from the left table. The result is NULL from the left side when there is no match.
SELECT orders.order_id, customers.customer_name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;
FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table. The result is NULL from the other table when there is no match.
SELECT orders.order_id, customers.customer_name
FROM orders
FULL JOIN customers ON orders.customer_id = customers.customer_id;
CROSS JOIN: Returns the Cartesian product of the two tables, meaning it returns all possible combinations of rows.
SELECT orders.order_id, customers.customer_name
FROM orders
CROSS JOIN customers;
SELF JOIN: A regular join but the table is joined with itself.
SELECT a.employee_id, a.name, b.name AS manager_name
FROM employees a
INNER JOIN employees b ON a.manager_id = b.employee_id;
3.A primary key is a unique identifier for each record in a database table. It ensures that no two rows have the same value in the primary key column(s) and that the value is not null. For example, in a table of employees, the employee ID can be the primary key because each employee has a unique ID.
4.The different operators in SQL include: comparison operators(greater than, less than, equal to etc), logical operators(AND, OR, NOT), arithmetic operators(addition, subtraction, multiplication, division etc), bitwise operators(BITWISE AND, OR, NOT, XOR) , string operators(concatenation, IN, BETWEEN, LIKE) and special operators(IS NULL, IS NOT NULL).
5.SQL group functions like SUM, AVG, MIN, MAX, and COUNT are important for quickly adding up, finding averages, or counting data in databases. They help organize information by grouping similar data together. This makes it easier to analyze and understand large amounts of data.
6.In databases, relationships show how information in different tables connects. There are four kinds: one-to-one, where each item in one table matches one in another; one-to-many, where each item in one table can match many in another; many-to-one, where many items in one table match one in another; and many-to-many, where many items in both tables can match each other through a middle table. These links help keep data organized, avoid repeating it, and make it easier to find and understand.