- 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.
Vuyo Ngwane
Thitevhelwi Samuel Masuvhe
Lindokuhle Skosana
0.) Difference between SQL and MySQL:
SQL (Structured Query Language): SQL is a standard language used for managing and manipulating relational databases. It is used to perform tasks such as querying data, updating data, inserting/deleting data, and creating/modifying database structures.
MySQL: MySQL is a specific implementation of a relational database management system (RDBMS) that uses SQL as its query language. MySQL is popular for its ease of use, performance, and wide adoption in web applications.
1.) DBMS and its types:
DBMS (Database Management System): A DBMS is software designed to manage databases. It provides an interface for users and applications to interact with the database by storing, retrieving, and manipulating data.
Types of DBMS:
Relational DBMS (RDBMS): Stores data in a structured format using tables with rows and columns. Examples include MySQL, PostgreSQL, Oracle Database.
NoSQL DBMS: Stores and retrieves data using methods other than the traditional table structures. Examples include MongoDB (document-based), Cassandra (wide-column store), Redis (key-value store).
2.) 3. Types of joins in SQL:
SQL supports different types of joins to combine rows from multiple tables based on a related column between them. The main types are:
INNER JOIN: Returns rows when there is a match in both tables.
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
FULL JOIN (or FULL OUTER JOIN): Returns all rows when there is a match in either left or right table. Returns NULLs where there is no match.
3.) A Primary key is a unique identifier for a record in a database table. It uniquely identifies each record in the table and ensures no duplicate values are entered into the primary key column. It also establishes a relationship between tables (if used as a foreign key in another table).
4.) SQL does provide a number of operators for various data processing and querying activities. -Arithmetic Operators, which are employed in computations with numbers. + - * / %. Operators for comparison: these are used to compare values and provide Boolean results. != or = > \ >= = >. Logical operators are employed to evaluate and combine several circumstances. AND, OR, NOT.
5.) 5. Data Summarization: Group functions let you distill big datasets into insightful synopses. You can get aggregated numbers, such as the total, average, minimum, maximum, or count of particular columns, rather than examining individual rows.
Grouping by Categories: To organize your data into groups according to specific standards, you can utilize group functions in conjunction with the GROUP BY clause. This lets you figure out total values for every group, giving you an idea of how various categories stack up against one another.
Data analysis and reporting: The creation of dashboards and reports depends heavily on group functions. They aid in addressing inquiries such as:
Which customer segment's average order value is that?
Which product category brings in the most money?
What is the size of each department's workforce?
Making decisions: High-level overviews of your data are provided by aggregate results from group functions, which aid in well-informed decision-making. For instance, understanding the typical customer satisfaction score might help direct customer service enhancements.
6.) 6. Within the framework of databases, a relationship establishes the connections between several tables. By creating a connection between data in two or more tables, it makes it possible to compile and obtain relevant data. In SQL, there are three primary kinds of relationships:
One-on-One Connection:
A maximum of one record in each table corresponds to a record in the other table, and vice versa.
Example: A passport is only valid for a single person and can only be held by one individual.
One-to-Many Connection:
A record in one table can have many associations with records in another, but only one association can exist between a record in the second table and a record in the first table.
Example: Although a customer may place several orders, each order is unique to that customer.
Many-to-Many Connection:
Multiple records in the other table may be related with entries in both tables.
For instance, a student may sign up for more than one course, and a course may have more than one student. To manage the associations, this calls for the creation of a junction table, an additional table.