QBE (Query By Example)

Summary

The video explains SQL commands used for querying and manipulating data in a database, focusing on four key operations: selecting, inserting, updating, and deleting records. It covers the syntax for displaying all or specific columns with conditions, deleting records based on criteria, updating data with set conditions, and querying data from multiple tables. Examples include displaying student names based on certain criteria, deleting student records from a specific city, and updating student scores. The tutorial is aimed at helping users understand the basics of SQL commands through practical examples.

Takeaways

• 😀 'pe.co' or 'print' is used to display data in SQL queries.
• 😀 The 'insert' command is used to add new records in a database.
• 😀 'delete' command is used to remove records from the database.
• 😀 'update' command helps modify existing records in a database.
• 😀 SQL query example: 'select Bintang from mahasiswa' displays all student data from the 'mahasiswa' table.
• 😀 You can display specific columns (e.g., NPM, name, address, gender) from a table using SQL queries.
• 😀 Use conditions in SQL queries to filter data, such as selecting students from a specific city (e.g., Depok) or gender (e.g., W).
• 😀 Underscores in SQL queries can be used to match partial values, such as 'Depokqq' and 'W' with an underscore.
• 😀 The 'delete' command can be used to remove records with specific conditions, like students living in Bogor.
• 😀 The 'update' command allows modification of specific column values, like changing the 'mid' value for a student with a particular NPM.
• 😀 SQL queries can join multiple tables (e.g., 'student' and 'value') to filter data based on conditions from both tables, such as displaying students with values above 69 but less than 80.

Q & A

What is the purpose of the 'pe.co' command in SQL?

'pe.co' is used to display data in SQL, allowing the user to show the contents of a table.

What is the 'insert' command used for in SQL?

The 'insert' command is used to add new records to a table in SQL.

How does the 'delete' command work in SQL?

The 'delete' command is used to remove records from a table based on specified conditions.

What does the 'update' command do in SQL?

The 'update' command is used to modify existing records in a table, such as changing the values in one or more columns.

Can you explain how to display all data from the 'mahasiswa' table?

To display all data from the 'mahasiswa' table, you would use the command 'select * from mahasiswa'. This will show all the columns and their respective data.

How would you display names from the 'mahasiswa' table where the city is 'Depok' and the gender is 'W'?

You would use the SQL query 'select name from mahasiswa where city = 'Depok' and gender = 'W'. This query will display the names of female students who live in Depok.

What does the underscore (_) represent in SQL queries?

In SQL, an underscore (_) is used as a wildcard to represent a single character in a string, often for pattern matching or search operations.

How do you delete records for students living in 'Bogor'?

To delete records for students living in 'Bogor', you would use the 'delete' command with the condition 'where city = 'Bogor'. The query would look like: 'delete from mahasiswa where city = 'Bogor'.

How can you update the 'mid' value to 90 for a student with NPM '12345'?

You can use the 'update' command with a condition specifying the NPM. The query would be: 'update mahasiswa set mid = 90 where npm = '12345'. This will update the mid value for the student with NPM '12345'.

How would you retrieve the names and NPMs of students who have a 'mid' value between 69 and 80?

To retrieve this information, you would join the 'mahasiswa' and 'nilai' tables, using a query like: 'select mahasiswa.name, mahasiswa.npm from mahasiswa, nilai where mahasiswa.npm = nilai.npm and nilai.mid > 69 and nilai.mid < 80'. This will display the names and NPMs of students whose 'mid' values are between 69 and 80.