SQL | Geeky Codes- Code Like A Geek But In Style

24 Oct

Most Popular SQL Commands everyone should know

geekycodesco0 CommentsSQL

SQL Commands:SQL Commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data. SQL can perform various tasks like creating a table. add data to tables, drop the table modify the table. set permission for users.Types of SQL Commands: There are five types of SQL Commands: Data Definition Language (DDL) DDL changes the table's structure like creating a table deleting a table, altering a table, etc.All the commands of DDL are auto-committed. That means it permanently saves all the changes in the databaseHere are some commands that…

13 Oct

What’s a natural Join in SQL?

geekycodesco0 CommentsSQLProgramming, SQL, SQL Queries

You’ve heard of inner, left, right, and outer joins, but do you know about NATURAL JOINS?? USING clause, which allows you to shortcut your writing of joins by only requiring the joining columns to be typed out once and returns those columns only once when you SELECT * from all your joined tables Well, natural joins take that one step further, by eliminating the need to type out any join expression or join columns at all. The only thing you need to ensure is that your joining columns are the same name and data type across your associated tables being…

03 Oct

𝗡𝗲𝘄 𝗮𝗻𝗱 𝗥𝗲𝗽𝗲𝗮𝘁 𝗖𝘂𝘀𝘁𝗼𝗺𝗲𝗿𝘀 𝗹𝗼𝗴𝗶𝗰 𝗶𝗻 𝗦𝗤𝗟

geekycodesco0 CommentsSQL, Tutorials

𝙒𝙝𝙮 𝙘𝙖𝙡𝙘𝙪𝙡𝙖𝙩𝙚 𝙧𝙚𝙥𝙚𝙖𝙩 𝙘𝙪𝙨𝙩𝙤𝙢𝙚𝙧𝙨?1. Repeat Customers spend more on each purchase2. Repeat Customers are more likely to shop with you again and again3. Repeat Customers are our business promoters4. Businesses spend more on your key times like festive rush 𝙒𝙝𝙮 𝙣𝙚𝙬 𝙘𝙪𝙨𝙩𝙤𝙢𝙚𝙧𝙨 𝙖𝙧𝙚 𝙞𝙢𝙥𝙤𝙧𝙩𝙖𝙣𝙩?1. Customer Acquisition Cost (CAC) is an important metric. The lesser the CAC, the better the business is.2. Helps businesses to identify if their model is a viable one.3. Every business must understand their CAC as it helps them in investing wisely and scale their business.4. They get an idea of how much revenue is needed for…

14 Sep

A cool SQL trick you probably never knew you could do using CASE statements: PIVOTING!

geekycodesco0 CommentsSQL

Pivoting your data from long format (where values repeat in the first column) to wide format (where values do NOT repeat in the first column) comes in handy when you need to turn unique values in a column into individual columns. You can certainly use the PIVOT function to do so, but not all warehouses support PIVOT since it is a newer function, and its syntax can be hard to remember. Enter the trusty CASE statement to get the job done, no matter what warehouse you’re working in! Pivoting using CASE aggregates by the column for which you want to…

17 Mar

How to use the LAG function in SQL?

geekycodesco0 CommentsSQLLAG Function, LAG Function in sql, SQL, SQL Queries

LAG function in sql is used when we have data based on time and we want to replace the current data from the previous month's data. In Oracle, you could use the LAG function within a case statement like so: If the data looks like this: MONTH | Value ___________________ JAN2016 | 100.00 FEB2016 | MAR2016 | 342.60 APR2016 | 450.20 Code: SELECT MONTH, CASE WHEN Value is null then LAG(Value OVER MONTH,1) else Value END as Value FROM myTable; The result would be: MONTH | Value ________________________ JAN2016 | 100.00 FEB2016 | 100.00 MAR2016 | 342.60 APR2016 | 450.20 This solution will work…

23 Jan

Starting Data Pipelines | Fundamentals of Data Engineering

geekycodesco0 CommentsData Engineering, Data Pipelines, Data Science, Python, SQLData Engineering, data pipelines, Ingestion, Programming, Python, SQL

This article includes a comprehensive introduction with step-by-step definitions and code in data pipelines to introduce the basics of data engineering. Data pipelines are widely used in data science and machine learning and are essential in the process of machine learning to integrate data from multiple streams to gain business intelligence for competitive and profitable analysis. What is a Data Pipeline? Data pipeline is a set of rules that motivates and converts data from multiple sources to an area where new values can be obtained. In the simplest way, the pipeline can only extract data from various sources such as…

29 Jul

The SQL SELECT Statement

geekycodesco0 CommentsSQLDatabase, Select Statement, SQL

Select Statement is used for selecting data from a database. Data Returned in this query is stored in a table. Select Syntax SELECT column1, column2, ... FROM table_name; Here column1,column2,...... are the column names you want to select from the table. You can select all columns at once using * instead of column names one by one. SELECT * FROM Table_name Sample Database Below is a sample Employee Table WORKER_IDFIRST_NAMELAST_NAMESALARYJOINING_DATEDEPARTMENT001MonikaArora1000002014-02-20 09:00:00HR002NiharikaVerma800002014-06-11 09:00:00Admin003VishalSinghal3000002014-02-20 09:00:00HR004AmitabhSingh5000002014-02-20 09:00:00Admin005VivekBhati5000002014-06-11 09:00:00Admin006VipulDiwan2000002014-06-11 09:00:00Account007SatishKumar750002014-01-20 09:00:00Account008GeetikaChauhan900002014-04-11 09:00:00Admin SELECT Column Example The following SQL statement selects the "FIRST_NAME" and "SALARY" columns from the "EMPLOYEE" table: SELECT FIRST_NAME, SALARY FROM EMPLOYEE…

19 Apr

SQL Syntax

geekycodesco0 CommentsBlog, Interview, SQL, Tutorials

Database A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just database. Each Database often contains one or more tables. Each Table may have different columns e.g. Customer Name, Contact Number, Address. Tables contain records(rows) with data. SQL Syntax SQL follows some unique set of rules and guidelines called syntax. Here, we are providing all the…