Durgesh Mahajan

Posted on Jan 23, 2022

SQL Beginner Friendly Cheatsheet

#sql #database #programming #computerscience

1. Retrieving/Selecting Data -

The SELECT statement is used to select data from a database.

SELECT column_name FROM table_name;

for eg -

SELECT StudentName FROM Student;

2. Selecting All Records/Columns of Data -

The Asterisk(*) is used to select all records.

SELECT * FROM table_name;

for eg -

SELECT * FROM Student;

3. Selecting Distinct Data -

The SELECT DISTINCT statement is used to return only distinct (different) values.

SELECT DISTINCT column_name FROM table_name;

for eg -

SELECT DISTINCT StudentName FROM Student;

4. Filtering the Records -

The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.

SELECT column_name FROM table_name WHERE condition;

for eg -

SELECT * FROM Student WHERE Marks>=450;

5. Filtering the Records by two or more conditions -

The WHERE clause can be combined with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on more than one condition.
The NOT operator displays a record if the condition(s) is NOT TRUE.

SELECT column_name FROM table_name WHERE condition1 AND condition2 OR condition3;

SELECT column_name FROM table_name WHERE NOT condition;

for eg -

SELECT * FROM Student WHERE Marks>=450 AND Div="A";

SELECT * FROM Student WHERE NOT Marks=500;

6. Sorting the Data of Records -

The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

SELECT column_name FROM table_name ORDER BY column_name;

for eg -

SELECT * FROM Student ORDER BY Marks DESC;

7. Inserting Data -

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table.

for eg -

INSERT INTO Student (StudentName, Class, Div, Marks)
VALUES ("Durgesh", "Second Year", "B", 450);

8. Updating Data -

The UPDATE statement is used to modify the existing records in a table.
If you omit the WHERE clause, ALL records will be updated!

UPDATE table_name SET column=value WHERE condition;

for eg -

UPDATE Student SET Marks=440 WHERE StudentName="Durgesh";

Follow for more :-)

Linkedin
Github
Instagram

Top comments (1)

Rick Hoek • Jan 24 • Edited

Onle language for everything

We have created an amazing framework that turns SQL Server into an Application Server. We call it "T-SQL.APP". It is a web application with built-in code editor to add functionality to the app, like this word-chain game:

this is inside the web app in the code editor:

-- Declare all variables
DECLARE @State NVARCHAR(MAX);
DECLARE @CurrentWord NVARCHAR(MAX);
DECLARE @PreviousWord NVARCHAR(MAX);
DECLARE @UserInput NVARCHAR(MAX);
DECLARE @SubmitButton NVARCHAR(MAX);
DECLARE @QuitButton NVARCHAR(MAX);
DECLARE @Score INT;
DECLARE @LastLetter NVARCHAR(1);
DECLARE @Message NVARCHAR(MAX);
DECLARE @RestartJSON NVARCHAR(MAX);
DECLARE @TimeLeft INT;
DECLARE @GameTitle NVARCHAR(MAX);
DECLARE @WordChain NVARCHAR(MAX);  -- New variable for storing the chain

-- Synchronize all values
EXEC sp_api_modal_get_value @name='@State', @value=@State OUT;
EXEC sp_api_modal_get_value @name='@CurrentWord', @value=@CurrentWord OUT;
EXEC sp_api_modal_get_value @name='@PreviousWord', @value=@PreviousWord OUT;
EXEC sp_api_modal_get_value @name='@UserInput', @value=@UserInput OUT;
EXEC sp_api_modal_get_value @name='@SubmitButton', @value=@SubmitButton OUT;
EXEC sp_api_modal_get_value @name='@QuitButton', @value=@QuitButton OUT;
EXEC sp_api_modal_get_value @name='@Score', @value=@Score OUT;
EXEC sp_api_modal_get_value @name='@TimeLeft', @value=@TimeLeft OUT;
EXEC sp_api_modal_get_value @name='@WordChain', @value=@WordChain OUT;

-- Initialize game state if first run
IF @State IS NULL 
BEGIN
    SET @State = N'START';
    SET @Score = 0;
    SET @TimeLeft = 30;
    SET @PreviousWord = N'ELEPHANT'; -- Starting word
    SET @WordChain = @PreviousWord;  -- Initialize chain with first word
END

-- Set game title with fancy emojis
SET @GameTitle = N'🔤 Word Chain Challenge 🎯';

-- Display game header
EXEC sp_api_modal_text @text=@GameTitle, @class='h2';

-- Game logic based on state
IF @State = N'START'
BEGIN
    -- Display current chain
    SET @Message = CONCAT(N'Current Chain: ', @WordChain);
    EXEC sp_api_modal_text @text=@Message, @class='h5';

    SET @Message = CONCAT(
        N'Enter a word that starts with "', UPPER(RIGHT(@PreviousWord, 1)), N'"'
    );
    EXEC sp_api_modal_text @text=@Message;

    -- Show score
    SET @Message = CONCAT(N'Current Score: ', @Score, N' | Time Left: ', @TimeLeft, N' seconds');
    EXEC sp_api_modal_text @text=@Message, @class='h4';

    -- Input field for next word
    EXEC sp_api_modal_input 
        @name='@UserInput',
        @value=@UserInput OUT,
        @placeholder=N'Type your word here...',
        @class='form-control';

    -- Submit button
    EXEC sp_api_modal_button
        @name='@SubmitButton',
        @value=N'Submit Word! 📝',
        @valueout=@SubmitButton OUT,
        @class='btn-primary';

    -- Handle word submission
    IF @SubmitButton IS NOT NULL AND @UserInput IS NOT NULL
    BEGIN
        SET @LastLetter = UPPER(RIGHT(@PreviousWord, 1));

        -- Validate word
        IF LEFT(UPPER(@UserInput), 1) != @LastLetter
        BEGIN
            -- Invalid word - show error
            SET @Message = CONCAT(N'❌ Your word must start with the letter "', @LastLetter, N'"!');
            EXEC sp_api_toast @text=@Message, @class='btn-danger';
        END
        ELSE
        BEGIN
            -- Valid word - update score and continue
            SET @Score = @Score + LEN(@UserInput);
            SET @TimeLeft = @TimeLeft - 1;
            SET @PreviousWord = @UserInput;
            -- Update word chain
            SET @WordChain = CONCAT(@WordChain, N' ➡️ ', @UserInput);

            -- Show success message
            SET @Message = CONCAT(N'✨ Great word! +', CAST(LEN(@UserInput) AS NVARCHAR(10)), N' points!');
            EXEC sp_api_toast @text=@Message, @class='btn-success';

            -- Check if time's up
            IF @TimeLeft <= 0
            BEGIN
                SET @State = N'GAMEOVER';
            END

            -- Prepare JSON for next round
            SET @RestartJSON = (
                SELECT 
                    [@State] = @State,
                    [@Score] = @Score,
                    [@PreviousWord] = @PreviousWord,
                    [@TimeLeft] = @TimeLeft,
                    [@WordChain] = @WordChain
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            );

            EXEC sp_api_modal_restart @values=@RestartJSON;
            RETURN;
        END
    END
END

IF @State = N'GAMEOVER'
BEGIN
    -- Show final score with fancy formatting
    SET @Message = N'🎮 Game Over! 🎮';
    EXEC sp_api_modal_text @text=@Message, @class='h2';

    -- Display the final chain
    SET @Message = N'🔗 Your Word Chain:';
    EXEC sp_api_modal_text @text=@Message, @class='h3';
    EXEC sp_api_modal_text @text=@WordChain;

    SET @Message = CONCAT(
        N'🏆 Final Score: ', @Score, 
        N'↵🎯 Words Used: ', @Score / 5, -- Approximate number of words
        N'↵⭐ Average Word Length: ', @Score / (@Score / 5)
    );
    EXEC sp_api_modal_text @text=@Message;

    -- Add some fun feedback based on score
    SET @Message = CASE 
        WHEN @Score > 100 THEN N'🌟 Vocabulary Master!'
        WHEN @Score > 50 THEN N'✨ Word Wizard!'
        ELSE N'📚 Keep Reading!'
    END;
    EXEC sp_api_modal_text @text=@Message, @class='h3';

    -- Play again button
    EXEC sp_api_modal_button
        @name='@SubmitButton',
        @value=N'Play Again! 🎮',
        @valueout=@SubmitButton OUT,
        @class='btn-success';

    IF @SubmitButton IS NOT NULL
    BEGIN
        SET @RestartJSON = (
            SELECT 
                [@State] = N'START',
                [@Score] = 0,
                [@PreviousWord] = N'ELEPHANT',
                [@TimeLeft] = 30,
                [@WordChain] = N'ELEPHANT'
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );

        EXEC sp_api_modal_restart @values=@RestartJSON;
        RETURN;
    END
END

-- Quit button (always available)
EXEC sp_api_modal_button
    @name='@QuitButton',
    @value=N'Exit Game 🚪',
    @valueout=@QuitButton OUT,
    @class='btn-secondary';

IF @QuitButton IS NOT NULL
BEGIN
    EXEC sp_api_modal_clear;
    EXEC sp_api_toast @text=N'Thanks for playing! 👋', @class='btn-info';
    RETURN;
END
/* 
-- Declare all variables
DECLARE @State NVARCHAR(MAX);
DECLARE @CurrentWord NVARCHAR(MAX);
DECLARE @PreviousWord NVARCHAR(MAX);
DECLARE @UserInput NVARCHAR(MAX);
DECLARE @SubmitButton NVARCHAR(MAX);
DECLARE @QuitButton NVARCHAR(MAX);
DECLARE @Score INT;
DECLARE @LastLetter NVARCHAR(1);
DECLARE @Message NVARCHAR(MAX);
DECLARE @RestartJSON NVARCHAR(MAX);
DECLARE @TimeLeft INT;
DECLARE @GameTitle NVARCHAR(MAX);

-- Synchronize all values
EXEC sp_api_modal_get_value @name='@State', @value=@State OUT;
EXEC sp_api_modal_get_value @name='@CurrentWord', @value=@CurrentWord OUT;
EXEC sp_api_modal_get_value @name='@PreviousWord', @value=@PreviousWord OUT;
EXEC sp_api_modal_get_value @name='@UserInput', @value=@UserInput OUT;
EXEC sp_api_modal_get_value @name='@SubmitButton', @value=@SubmitButton OUT;
EXEC sp_api_modal_get_value @name='@QuitButton', @value=@QuitButton OUT;
EXEC sp_api_modal_get_value @name='@Score', @value=@Score OUT;
EXEC sp_api_modal_get_value @name='@TimeLeft', @value=@TimeLeft OUT;

-- Initialize game state if first run
IF @State IS NULL 
BEGIN
    SET @State = N'START';
    SET @Score = 0;
    SET @TimeLeft = 30;
    SET @PreviousWord = N'ELEPHANT'; -- Starting word
END

-- Set game title with fancy emojis
SET @GameTitle = N'🔤 Word Chain Challenge 🎯';

-- Display game header
EXEC sp_api_modal_text @text=@GameTitle, @class='h2';

-- Game logic based on state
IF @State = N'START'
BEGIN
    SET @Message = CONCAT(
        N'The game starts with the word: ', @PreviousWord, 
        N'↵Enter a word that starts with "', UPPER(RIGHT(@PreviousWord, 1)), N'"'
    );
    EXEC sp_api_modal_text @text=@Message;

    -- Show score
    SET @Message = CONCAT(N'Current Score: ', @Score, N' | Time Left: ', @TimeLeft, N' seconds');
    EXEC sp_api_modal_text @text=@Message, @class='h4';

    -- Input field for next word
    EXEC sp_api_modal_input 
        @name='@UserInput',
        @value=@UserInput OUT,
        @placeholder=N'Type your word here...',
        @class='form-control';

    -- Submit button
    EXEC sp_api_modal_button
        @name='@SubmitButton',
        @value=N'Submit Word! 📝',
        @valueout=@SubmitButton OUT,
        @class='btn-primary';

    -- Handle word submission
    IF @SubmitButton IS NOT NULL AND @UserInput IS NOT NULL
    BEGIN
        SET @LastLetter = UPPER(RIGHT(@PreviousWord, 1));

        -- Validate word
        IF LEFT(UPPER(@UserInput), 1) != @LastLetter
        BEGIN
            -- Invalid word - show error
            SET @Message = CONCAT(N'❌ Your word must start with the letter "', @LastLetter, N'"!');
            EXEC sp_api_toast @text=@Message, @class='btn-danger';
        END
        ELSE
        BEGIN
            -- Valid word - update score and continue
            SET @Score = @Score + LEN(@UserInput);
            SET @TimeLeft = @TimeLeft - 1;
            SET @PreviousWord = @UserInput;

            -- Show success message
            SET @Message = CONCAT(N'✨ Great word! +', CAST(LEN(@UserInput) AS NVARCHAR(10)), N' points!');
            EXEC sp_api_toast @text=@Message, @class='btn-success';

            -- Check if time's up
            IF @TimeLeft <= 0
            BEGIN
                SET @State = N'GAMEOVER';
            END

            -- Prepare JSON for next round
            SET @RestartJSON = (
                SELECT 
                    [@State] = @State,
                    [@Score] = @Score,
                    [@PreviousWord] = @PreviousWord,
                    [@TimeLeft] = @TimeLeft
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            );

            EXEC sp_api_modal_restart @values=@RestartJSON;
            RETURN;
        END
    END
END

IF @State = N'GAMEOVER'
BEGIN
    -- Show final score with fancy formatting
    SET @Message = N'🎮 Game Over! 🎮';
    EXEC sp_api_modal_text @text=@Message, @class='h2';

    SET @Message = CONCAT(
        N'🏆 Final Score: ', @Score, 
        N'↵🎯 Words Used: ', @Score / 5, -- Approximate number of words
        N'↵⭐ Average Word Length: ', @Score / (@Score / 5)
    );
    EXEC sp_api_modal_text @text=@Message;

    -- Add some fun feedback based on score
    SET @Message = CASE 
        WHEN @Score > 100 THEN N'🌟 Vocabulary Master!'
        WHEN @Score > 50 THEN N'✨ Word Wizard!'
        ELSE N'📚 Keep Reading!'
    END;
    EXEC sp_api_modal_text @text=@Message, @class='h3';

    -- Play again button
    EXEC sp_api_modal_button
        @name='@SubmitButton',
        @value=N'Play Again! 🎮',
        @valueout=@SubmitButton OUT,
        @class='btn-success';

    IF @SubmitButton IS NOT NULL
    BEGIN
        SET @RestartJSON = (
            SELECT 
                [@State] = N'START',
                [@Score] = 0,
                [@PreviousWord] = N'ELEPHANT',
                [@TimeLeft] = 30
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );

        EXEC sp_api_modal_restart @values=@RestartJSON;
        RETURN;
    END
END

-- Quit button (always available)
EXEC sp_api_modal_button
    @name='@QuitButton',
    @value=N'Exit Game 🚪',
    @valueout=@QuitButton OUT,
    @class='btn-secondary';

IF @QuitButton IS NOT NULL
BEGIN
    EXEC sp_api_modal_clear;
    EXEC sp_api_toast @text=N'Thanks for playing! 👋', @class='btn-info';
    RETURN;
END
*/

DEV Community

SQL Beginner Friendly Cheatsheet

1. Retrieving/Selecting Data -

2. Selecting All Records/Columns of Data -

3. Selecting Distinct Data -

4. Filtering the Records -

5. Filtering the Records by two or more conditions -

6. Sorting the Data of Records -

7. Inserting Data -

8. Updating Data -

Follow for more :-)

Top comments (1)

Onle language for everything

Read next

Debugging HTTPS localhost: httponly cookie issues

Fixing '@layer utilities...' Tailwind Error: A Quick Guide

Passing variables from the static page to the widget

How To Increase Performance of Web APIs in .NET