Quiz

  1. What’s the difference between SQL and mySQL?

    SQL is an industry-standard language. MySQL is some DBMS software that you can talk to using SQL. There are other implementations of a DBMS, such as Oracle, Postgres, IBM's DB2, and others.

  2. Is there any difference between calling it a clause versus a statement in mysql that we should know of?

    A clause is a part of a statement. So

    
        SELECT name, birthdate
        FROM person
        WHERE nm > 100 and nm < 200
        ORDER by name;                            
    
    

    has four clauses, the capitalized keywords

  3. Can we please go over in more depth about client-server architecture and the difference between the mySQL server and mySQL clients?
    Can you clarify the client-server architecture of MySQL? Is MySQLd running separately on a server, which we then connect to as clients when we use MySQL?
    Could you explain more about what a client-server Architecture is?

    When the CS server boots, a daemon process starts up that controls access to the data in the database. This process is called mysqld, and it ends with a "d" because it's a daemon. (This is standard Unix terminology, not just databases. The Apache software runs as httpd, because it's also a daemon.)

    When we run the mysql command, we start a second process (a client) that connects to the daemon (the server). When we type a SQL statement, our client sends it to the daemon, which executes it and sends back the results.

  4. What do you mean by "There can be multiple tables" in a Relational DBMS like MySQL?

    We might have a table of movies, a table of people, a table of production companies, a table of suppliers, a table of locations, a table of ...

  5. I understand that a database is a collection of tables and that a DBMS manages many databases. Does each table in a database represent a specific set of data, such as the student list for Wellesley College?

    Exactly right. So there might be a table of students, and a separate one of faculty, another of departments, another of vendors ...

  6. Just a clarifying question- Is there a table for each separate database in a rdbms? And is there a big difference between accessing information across different databases in a rdbms vs a non-relational dbms?

    In MySQL, a database is a collection of tables (usually related). The DBMS might manage lots of different databases.

    We'll talk more about non-relational databases later in the course, once we understand joins and such. We'll get there!

  7. Can I select information from more than one table at once to use in a query?

    Yes! That's called a "join" and we'll learn it next time!

  8. How do we search for rows that may include certain words / numbers? Can we use a wildcard like with Unix?

    Yes! Next Friday! Feel free to read ahead.

  9. Can different rows have different input types for one specific column? E.g for the birthdate column can one row have it set to 10/15/1999 and another set theirs to December 25th, 2005? Or does it all have to be the same format/type?

    No. For efficiency, each item has the same representation. For dates, I'm pretty sure they are represented like Unix dates, which is the number of milliseconds since January 1, 1970. But the SQL language offers a ton of ways of formatting the dates (just like Python does). So in practice, if you want to search for dates that contain the string "ember", you could do that, by formatting the date value and then searching the string representation using wildcards.

  10. Is the date/time a special type of variable/sth different from a regular number?

    yes. There are constraints on dates. No February 30th or January 32nd. This is a good thing, helping our data integrity.

  11. I understand the different queries that you can use to look up certain rows in the tables but is there a way to actually edit the values in the tables via a batch file for example?
    If we test for equality using a single equals sign, not two, how do we assign/what do we use to assign a value?

    Yes! September 17th. We'll get there soon.

  12. How would we construct the table which queries are getting the information out of?

    Also Sept 17th.

  13. For a batch file, can you use the "USE" command and the query commands twice to get information from two different databases in one file?

    Yes! This is a great idea. You can put several, even lots, of queries into a sincle batch file. That's really why it's called a "batch" file.

  14. Are you able to store more than just numbers and strings into a cell?

    MySQL has a lot of datatypes. We'll learn about some on Sept 17th. But you can also look at MySQL data-types

  15. Also, how are batch files different from Python files? In other words, what aspect/feature of batch files makes them worthy of their own label? Can't we run multiple lines of commands in Python as well?

    Great question! They are similar in that they are text files that contain executable code in a particular language (SQL, Python). But there's is also a REPL (read-eval-print-loop) provided by implementation of SQL and implementations of Python. So why would you write a batch file? Because you want to write down (and debug) some code that you will run again later, or hand to someone else to run, etc.

    It's a little like the difference between a live conversation and a document, that can be edited and re-read many times.

  16. Does the source method not exiting the MySQL Interpreter mark any differences at times during operation or is it just a personal preference?

    Personal preference, mostly.

  17. What does it mean printed differently in ""It doesn't say that the database has changed. That's because things are printed differently.""

    When we run a batch file from the command line, the SQL interpreter prints the results in a different way, omitting many of the chattier messages. This will be useful when we want to run a batch file that prints some data, so that we can capture that data into an output file, like a CSV file. We'll do that in a few weeks. Until then, it's pretty much personal preference.

  18. What are some of the best practices for optimizing the performance of mySQL queries while working with huge complex databases?

    Oh, that's a great question, but let's leave it for another time, or maybe we can talk during office hours.