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

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…