If you're seeing this message, it means we're having trouble loading external resources on our website.

If you're behind a web filter, please make sure that the domains *.kastatic.org and *.kasandbox.org are unblocked.

Main content

Using SQL to update a database

As we've mentioned throughout this course, there are many times we might find ourselves using SQL or a SQL-like query language on a database. We can think of some uses as "read-only operations" and other uses as "read/write operations".
An example of a "read-only operation" is a data analysis on a data dump from some app or research study. For example, if I was a data scientist working for a daily diary, I might query what percentage of users eat ice cream on the same day that they run, to understand if exercise makes people want to reward themselves:
SELECT * FROM diary_logs WHERE
       food LIKE "%ice cream%" AND activity LIKE "%running%";
If I'm doing a data analysis like that, then pretty much everything I'm doing is a SELECT - it's all read only. We're not creating any new data, we are just querying existing data. We need to get very good at SELECT queries, but we don't need to know how to create tables, update rows, and all of that.
An example of "read/write operations" is a software engineer creating the backend for a webapp. For example, if I was the software engineer working on the health tracker, I might write code that knows how to insert a new daily log in the database every time a user submits a form:
INSERT INTO diary_logs (id, food, activity)
            VALUES (123, "ice cream", "running");
I would probably be issuing that SQL command from inside a server-side language, likely using a library to make it easier to construct the commands. This is what that insertion would look like if I was using Python with the SQLAlchemy library:
diary_logs.insert().values(id=123, food="ice cream", activity='running')
I would also need to write SQL to modify the database if the user edited their daily log, deleted it, or even deleted their account. If the team decided to add more features to the user's daily log, like an emotion column to track how happy while they were eating ice cream and running (my hypothesis: very happy), I would need to write SQL to modify the table schema itself.
Those are all "write operations", and they are necessary if we're going to use a database to store and update data for an app. INSERT is relatively safe, because all it does is add data, but operations like UPDATE, DELETE, DROP, or ALTER can be much more dangerous, because they are updating existing data. That's why it's important to really understand those well, and use them carefully. Keep going to learn how to use them!

Want to join the conversation?

  • orange juice squid orange style avatar for user Annie
    What does a library do? Is it like a list of saved commands/queries?
    (67 votes)
    Default Khan Academy avatar avatar for user
    • leaf green style avatar for user Lucie le Blanc
      Good question. Yes, a library is a set of pre-written commands that are used to make life easier for the developer. Many companies and individuals create their own libraries in order to make everyday programming tasks simpler. Some libraries contain very useful commands and algorithms that would normally take hundreds of lines to write out, so they help programmers save time and energy. Most modern programming languages (Python, Java, HTML/CSS, etc.) have libraries written for them that can be found online or accessed publicly. For example, Processing.js is a library written for JavaScript that is used in the drawing environment here on Khan Academy.
      (183 votes)
  • hopper cool style avatar for user Dhaval Furia
    The Python code has "running" enclosed in single quotes whereas "ice cream" is in double quotes.
    Is this a mistake ?
    (26 votes)
    Default Khan Academy avatar avatar for user
  • purple pi purple style avatar for user Js
    If the schema is changed, that all existing data will be affected. Is there any way to avoid modifying all data one by one? Thank you.
    (13 votes)
    Default Khan Academy avatar avatar for user
  • leafers seed style avatar for user Anh Hua
    Hi everyone,
    I'm taking this Intro to SQL course because I want to get a job as a Research Analyst in Institutional Research and Planning at universities and colleges ( so far, most of the Research Analyst positions require knowledge of SQL). Is completing this course enough to help me get this type of job, assuming that I have all the other skills they want, e.g., stats, writing, etc? In other words, is the content in this course advanced enough for this position that I could impress my potential employers with my knowledge of the stuff we learned here? Any advice from people who have experience working in Institutional Research and Planning would be greatly appreciated. Many thanks!
    (8 votes)
    Default Khan Academy avatar avatar for user
    • starky ultimate style avatar for user SpencerGlove
      As far as languages go, SQL is not heavily involved. These courses will lay a solid foundation for your understanding and allow you to pick up where any employer will begin to train their people. As someone who trained SQL equivalent to people who had exposure and those who didn't, these courses are enough to get your foot in the door. Training people to think about database structure and data retrieval in a 4 day class is really hard if they haven't been exposed to the concepts. Best of luck!
      (23 votes)
  • orange juice squid orange style avatar for user no
    Could you keep your program safe by hiding the screen?
    (3 votes)
    Default Khan Academy avatar avatar for user
    • leaf grey style avatar for user Gregory G
      As far as I know you can't hide the screen or code on Khan Academy. If you mean in an actual website with SQL, making a database doesn't actually print it on the screen (that would mean trouble for banking companies for example) so I don't believe that hiding the screen to keep your program safe would be necessary. In real websites you do need to try and keep your data secure by protecting against measures such as SQL injections. (https://xkcd.com/327/ is a funny comic illustrating that exact thing happening).
      (11 votes)
  • blobby green style avatar for user jebasmsd7
    whatever we learnt using khan academy is enough before going to the IT company?
    (1 vote)
    Default Khan Academy avatar avatar for user
  • leaf green style avatar for user DJ
    Where's a good place to look that provides strategies on creating efficient and meaningful SELECT queries?
    (3 votes)
    Default Khan Academy avatar avatar for user
  • male robot hal style avatar for user AlAbbas14
    what is wrong with this code :
    CREATE TABLE persons (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    fullname TEXT,
    age INTEGER);

    INSERT INTO persons (fullname, age) VALUES ("Bobby McBobbyFace", "12");
    INSERT INTO persons (fullname, age) VALUES ("Lucy BoBucie", "25");
    INSERT INTO persons (fullname, age) VALUES ("Banana FoFanna", "14");
    INSERT INTO persons (fullname, age) VALUES ("Shish Kabob", "20");
    INSERT INTO persons (fullname, age) VALUES ("Fluffy Sparkles", "8");

    CREATE table hobbies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    person_id INTEGER,
    name TEXT);

    INSERT INTO hobbies (person_id, name) VALUES (1, "drawing");
    INSERT INTO hobbies (person_id, name) VALUES (1, "coding");
    INSERT INTO hobbies (person_id, name) VALUES (2, "dancing");
    INSERT INTO hobbies (person_id, name) VALUES (2, "coding");
    INSERT INTO hobbies (person_id, name) VALUES (3, "skating");
    INSERT INTO hobbies (person_id, name) VALUES (3, "rowing");
    INSERT INTO hobbies (person_id, name) VALUES (3, "drawing");
    INSERT INTO hobbies (person_id, name) VALUES (4, "coding");
    INSERT INTO hobbies (person_id, name) VALUES (4, "dilly-dallying");
    INSERT INTO hobbies (person_id, name) VALUES (4, "meowing");

    CREATE table friends (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    person1_id INTEGER,
    person2_id INTEGER);

    INSERT INTO friends (person1_id, person2_id)
    VALUES (1, 4);
    INSERT INTO friends (person1_id, person2_id)
    VALUES (2, 3);
    SELECT persons.fullname, hobbies.name
    From persons
    JOIN hobbies
    ON persons.id = hobbies.person_id;

    SELECT a.name, b.name
    From friends
    JOIN hobbies as a
    ON friends.person1_id = a.id;
    JOIN hobbies as b
    ON friends.person2_id = b.id;
    thank you
    (2 votes)
    Default Khan Academy avatar avatar for user
  • blobby green style avatar for user ochaetanetworking
    I'm studing for data science but my way now is to be a developer to understand everything from cero, in SQL I hear about DCL statements, what are those?
    (2 votes)
    Default Khan Academy avatar avatar for user
    • aqualine ultimate style avatar for user AD Baker
      DCL (data control language) statements are used in some flavors of SQL to mange users' ability to perform tasks in SQL. DCL statements grant or revoke permissions based on user IDs. This is to minimize the possibility of data being accidentally deleted or changed.

      SQLite, which we use on Khan Academy, does not use DCL commands. SQLite does not employ user IDs. The ability to edit data in SQLite depends on file system permissions.
      (6 votes)
  • blobby green style avatar for user Richard Mwangi
    oracle is a computing program that uses sql, plsql, isql and sqlplus do you have a site based on that? thank you.
    (3 votes)
    Default Khan Academy avatar avatar for user