Re: [R] SQL and R

[akshay kulkarni]

Dear Gabor,
  THanks a lot

THanking you,
Yours sincerely,
AKSHAY M KULKARNI

From: Gabor Grothendieck 
Sent: Thursday, December 12, 2024 7:39 PM
To: akshay kulkarni 
Cc: R help Mailing list 
Subject: Re: [R] SQL and R

The advantages of SQL are that
- it can be used from many languages so if you know SQL you can easily
move that part of your code to python, say,
and visa versa
- it is widely used
- it can handle data stored outside of R and possibly otherwise too large for R
- some SQL databases support multiple concurrent users
- depending on the database it may be used to communicate the data to others
- one big difference is that SQL is declarative rather than
imperative.  You tell it what you want rather
than how to get it and the optimizer will figure out how to create a
low level query to get the result.
For example, in the simple case below it will simply scan the BOD
table but in more complex cases
there may be many approaches and it will try to find a good one:

library(sqldf)
sqldf("explain query plan select * from BOD")
##   id parent notused   detail
## 1  2  0   0 SCAN BOD

sqldf("explain select * from BOD")
##addr   opcode p1 p2 p3   p4 p5 comment
## 1 0 Init  0 10  0   0  NA
## 2 1 OpenRead  0  2  02  0  NA
## 3 2   Rewind  0  9  0   0  NA
## 4 3   Column  0  0  1   0  NA
## 5 4 RealAffinity  1  0  0   0  NA
## 6 5   Column  0  1  2   0  NA
## 7 6 RealAffinity  2  0  0   0  NA
## 8 7ResultRow  1  2  0   0  NA
## 9 8 Next  0  3  0   1  NA
## 109 Halt  0  0  0   0  NA
## 11   10  Transaction  0  0  10  1  NA
## 12   11 Goto  0  1  0   0  NA

On Wed, Dec 11, 2024 at 8:17 AM akshay kulkarni  wrote:
>
> dear Members,
> I have recently started studying SQL and MySQL. 
> My question is, what exactly is SQL used for? That is, whatever can be done 
> by SQL, like subsetting and filtering of data sets, can also be done by R. 
> What's, then, the advantage of SQL?  It is OK if you tag this question as 
> offtopic, but I could'nt find any info on the web. Can you please refer me to 
> some online resources that shed some light on this? Finally, how does SQL 
> complement R? Are both dependent?
>
> THanking you,
> Yours sincerely,
> AKSHAY M KULKARNI
>
> [https://s-install.avcdn.net/ipm/preview/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif]<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
>   
> Virus-free.www.avast.com<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
>
> [[alternative HTML version deleted]]
>
> __
> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.



--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com

[[alternative HTML version deleted]]

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

Re: [R] SQL and R

[Gabor Grothendieck]

The advantages of SQL are that
- it can be used from many languages so if you know SQL you can easily
move that part of your code to python, say,
and visa versa
- it is widely used
- it can handle data stored outside of R and possibly otherwise too large for R
- some SQL databases support multiple concurrent users
- depending on the database it may be used to communicate the data to others
- one big difference is that SQL is declarative rather than
imperative.  You tell it what you want rather
than how to get it and the optimizer will figure out how to create a
low level query to get the result.
For example, in the simple case below it will simply scan the BOD
table but in more complex cases
there may be many approaches and it will try to find a good one:

library(sqldf)
sqldf("explain query plan select * from BOD")
##   id parent notused   detail
## 1  2  0   0 SCAN BOD

sqldf("explain select * from BOD")
##addr   opcode p1 p2 p3   p4 p5 comment
## 1 0 Init  0 10  0   0  NA
## 2 1 OpenRead  0  2  02  0  NA
## 3 2   Rewind  0  9  0   0  NA
## 4 3   Column  0  0  1   0  NA
## 5 4 RealAffinity  1  0  0   0  NA
## 6 5   Column  0  1  2   0  NA
## 7 6 RealAffinity  2  0  0   0  NA
## 8 7ResultRow  1  2  0   0  NA
## 9 8 Next  0  3  0   1  NA
## 109 Halt  0  0  0   0  NA
## 11   10  Transaction  0  0  10  1  NA
## 12   11 Goto  0  1  0   0  NA

On Wed, Dec 11, 2024 at 8:17 AM akshay kulkarni  wrote:
>
> dear Members,
> I have recently started studying SQL and MySQL. 
> My question is, what exactly is SQL used for? That is, whatever can be done 
> by SQL, like subsetting and filtering of data sets, can also be done by R. 
> What's, then, the advantage of SQL?  It is OK if you tag this question as 
> offtopic, but I could'nt find any info on the web. Can you please refer me to 
> some online resources that shed some light on this? Finally, how does SQL 
> complement R? Are both dependent?
>
> THanking you,
> Yours sincerely,
> AKSHAY M KULKARNI
>
> [https://s-install.avcdn.net/ipm/preview/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif]
>   
> Virus-free.www.avast.com
>
> [[alternative HTML version deleted]]
>
> __
> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.



-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

Re: [R] SQL and R

[akshay kulkarni]

Dear Eberhard,
Yes I did much research in google afetr the posting 
in this list. THe answers here and my research has made me understand SQL vis- 
a-vis  R pretty wellthanks agian,

THanking you,
Yours sincerely,
AKSHAY M KULKARNI


From: R-help  on behalf of Eberhard W Lisse 

Sent: Thursday, December 12, 2024 2:53 AM
To: r-help@r-project.org 
Subject: Re: [R] SQL and R

Looks like an assignment question.

If so, do your homework yourself. Google is your friend

el


On 2024-12-11 15:16, akshay kulkarni wrote:
> dear Members, I have recently started studying SQL and MySQL. My
> question is, what exactly is SQL used for? That is, whatever can be
> done by SQL, like subsetting and filtering of data sets, can also be
> done by R. What's, then, the advantage of SQL?  It is OK if you tag
> this question as offtopic, but I could'nt find any info on the web.

> light on this? Finally, how does SQL complement R? Are both
> dependent?
>
> THanking you, Yours sincerely, AKSHAY M KULKARNI

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

[[alternative HTML version deleted]]

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

Re: [R] SQL and R

[akshay kulkarni]

Dear Calum,
  THanks for the simple and practical answer.

Thanking you,
Yours sincerely
AKSHAY M KULKARNI

From: CALUM POLWART 
Sent: Thursday, December 12, 2024 4:22 AM
To: Sorkin, John 
Cc: akshay kulkarni ; R help Mailing list 
; avi.e.gr...@gmail.com ; Ben 
Bolker 
Subject: Re: [R] SQL and R


And to answer the dependency question.

Neither is dependent on the other. But both can be complimentary.

If you consider that SQL*may* be a route to accessing your data (if it's in a 
database).

And R *may* be a route to analysis of the data.

If the data is in a CSV file, Excel file, API etc.  you don't need SQL. IF it 
is in a database, you might extract it to CSV etc. or you might directly access 
it from R

If ALL you want is some simple number counts you can do that in SQL. NO NEED 
FOR R. So for instance you could have a database for a warehouse. There could 
be three tables in that database (you'll have far more).

Orders

Customers

Products

Products lists all your products - with a product code.

Customers lists all the customers with a customer code

And orders lists the customer code, date and product ordered.

SQL will let you create a "view" of that data that shows the customer address, 
and product name.

SQL can also tell you how many of a product were ordered by postal area in a 
month even though the data is not organised like that.

R can do that analysis too. But it could do far more statistical analysis and 
say run a stats test to see if male customers are more likely to buy beer than 
female.  I don't think that's possible in SQL.

Sometimes you could just pull the whole database into R and analyse it. But if 
you imagine that database was actually every sale made in Walmart in the last 
10 years, the database is huge.  It would be better to extract the beer sales 
and males and females only which is hopefully smaller to analyse...

On Wed, 11 Dec 2024, 21:39 Sorkin, John, 
mailto:jsor...@som.umaryland.edu>> wrote:
Dear Askay,

I believe my grey hair allows me to help answer your question. SQL, and its 
progenitor SEQUEL, were developed specifically to manipulate relational 
databases. It was developed in the early 1970s (equivalent to the historical 
bronze age) when the concept of a relational database (see 
https://en.wikipedia.org/wiki/Relational_database) and Codd's 12-rules were 
being developed (see https://en.wikipedia.org/wiki/Codd%27s_12_rules)

At the time, the concept of a relation database and a programming language 
dedicated to manipulating them was revolutionary. The concept was clearly 
needed, important, and well used; a commercial version of SQL, Oracle, made 
Larry Ellison more than a quarter billionaire.

S, one of the progenitors of R, was developed later. In 1975 by John Chambers, 
Rick Becker, Trevor Hastie, and William Cleveland (all of whom, I believe 
worked at Bell Labs) developed S as a general programming language. It was NOT 
developed specifically for the manipulation of relational databases. S had 
modest success in academia. S-Plus, a commercial version of R was developed 
fairly recently in 1988 by a company Statistical Sciences. The founder of 
Statistical Sciences was R. Douglas Marin who was a professor of statistics at 
the University of Washington, Seattle.

S was also the progenitor of R. R was developed by Ross Ihaka and Robert 
Gentlemen in 1993, faculty members of the University of Auckland. Given the 
ubiquity of R in academia, it is clear that S, much like SQL has been 
extraordinarily successful.

John



John David Sorkin M.D., Ph.D.
Professor of Medicine, University of Maryland School of Medicine;
Associate Director for Biostatistics and Informatics, Baltimore VA Medical 
Center Geriatrics Research, Education, and Clinical Center;
PI Biostatistics and Informatics Core, University of Maryland School of 
Medicine Claude D. Pepper Older Americans Independence Center;
Senior Statistician University of Maryland Center for Vascular Research;

Division of Gerontology and Paliative Care,
10 North Greene 
Street<https://www.google.com/maps/search/10+North+Greene+Street?entry=gmail&source=g>
GRECC (BT/18/GR)
Baltimore, MD 21201-1524
Cell phone 443-418-5382





From: R-help 
mailto:r-help-boun...@r-project.org>> on behalf 
of akshay kulkarni mailto:akshay...@hotmail.com>>
Sent: Wednesday, December 11, 2024 8:16 AM
To: R help Mailing  list
Subject: [R] SQL and R

dear Members,
I have recently started studying SQL and MySQL. My 
question is, what exactly is SQL used for? That is, whatever can be done by 
SQL, like subsetting and filtering of data sets, can also be done by R. What's, 
then, the advantage of SQL?  It is OK if you tag this question as offtopic, but 
I could'nt find any info on the web. Can you please refer me
QL complement R? Are

Re: [R] SQL and R

[akshay kulkarni]

Dear John,
THanks a lot.

THanking you,
Yours sincerely,
AKSHAY M KULKARNI

From: Sorkin, John 
Sent: Thursday, December 12, 2024 3:09 AM
To: akshay kulkarni ; R help Mailing list 
; avi.e.gr...@gmail.com ; Ben 
Bolker 
Subject: Re: SQL and R

Dear Askay,

I believe my grey hair allows me to help answer your question. SQL, and its 
progenitor SEQUEL, were developed specifically to manipulate relational 
databases. It was developed in the early 1970s (equivalent to the historical 
bronze age) when the concept of a relational database (see 
https://en.wikipedia.org/wiki/Relational_database) and Codd's 12-rules were 
being developed (see https://en.wikipedia.org/wiki/Codd%27s_12_rules)

At the time, the concept of a relation database and a programming language 
dedicated to manipulating them was revolutionary. The concept was clearly 
needed, important, and well used; a commercial version of SQL, Oracle, made 
Larry Ellison more than a quarter billionaire.

S, one of the progenitors of R, was developed later. In 1975 by John Chambers, 
Rick Becker, Trevor Hastie, and William Cleveland (all of whom, I believe 
worked at Bell Labs) developed S as a general programming language. It was NOT 
developed specifically for the manipulation of relational databases. S had 
modest success in academia. S-Plus, a commercial version of R was developed 
fairly recently in 1988 by a company Statistical Sciences. The founder of 
Statistical Sciences was R. Douglas Marin who was a professor of statistics at 
the University of Washington, Seattle.

S was also the progenitor of R. R was developed by Ross Ihaka and Robert 
Gentlemen in 1993, faculty members of the University of Auckland. Given the 
ubiquity of R in academia, it is clear that S, much like SQL has been 
extraordinarily successful.

John



John David Sorkin M.D., Ph.D.
Professor of Medicine, University of Maryland School of Medicine;
Associate Director for Biostatistics and Informatics, Baltimore VA Medical 
Center Geriatrics Research, Education, and Clinical Center;
PI Biostatistics and Informatics Core, University of Maryland School of 
Medicine Claude D. Pepper Older Americans Independence Center;
Senior Statistician University of Maryland Center for Vascular Research;

Division of Gerontology and Paliative Care,
10 North Greene Street
GRECC (BT/18/GR)
Baltimore, MD 21201-1524
Cell phone 443-418-5382





From: R-help  on behalf of akshay kulkarni 

Sent: Wednesday, December 11, 2024 8:16 AM
To: R help Mailing  list
Subject: [R] SQL and R

dear Members,
I have recently started studying SQL and MySQL. My 
question is, what exactly is SQL used for? That is, whatever can be done by 
SQL, like subsetting and filtering of data sets, can also be done by R. What's, 
then, the advantage of SQL?  It is OK if you tag this question as offtopic, but 
I could'nt find any info on the web. Can you please refer me
QL complement R? Are both dependent?

THanking you,
Yours sincerely,
AKSHAY M KULKARNI

[https://s-install.avcdn.net/ipm/preview/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif]
  
Virus-free.http://www.avast.com/

[[alternative HTML version deleted]]

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.r-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.




[[alternative HTML version deleted]]

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

Re: [R] SQL and R - tangential

[akshay kulkarni]

Dear JC,
 THanks .

THanking you,
Yours sincerely,
AKSHAY M KULKARNI

From: R-help  on behalf of J C Nash 

Sent: Wednesday, December 11, 2024 10:27 PM
To: r-help@r-project.org 
Subject: Re: [R] SQL and R - tangential

My late friend Morven Gentleman, not long after he stepped down from being chair
of Computer Science at Waterloo, said that it seemed computer scientists had to 
create
a new computer language for every new problem they encountered.

If we could use least squares to measure this approximation, we'd likely be 
suspicious
of a terribly small error measure or overly high R^2.

JN

On 2024-12-11 11:11, avi.e.gr...@gmail.com wrote:
> Akshay,
>
> Your question has way too many answers.
>
> SQL has a long history and early versions came long before R arrived on the
> scene. There is a huge embedded base of hardware and software dedicated to
> managing databases. It has some features that most R programs do not even
> dream of doing. Besides easily handling massive amounts of data or sometimes
> tweaking queries to possibly run more efficiently, there are all kinds of
> issue of how to manage multiple people accessing and changing the data at
> about the same time, or rolling the data back to an earlier checkpoint.
>
> R came along later and, as Ben pointed out, adds all kinds of things SQL
> does not have and likely does not need, or alternate ways to do things.
>
> For many people now, the workload is to use a programming language, and R is
> not the only one used, which has enhanced with packages or modules that
> allow access in a fairly general way to one or many databases running
> various versions of SQL. The programmer uses this API in many ways.
>
> In some ways, it is just a way to tell the database what to do without much
> other processing. You can ask to open a connection to the server, do a query
> that gets translated to SQL (or you can provide the actual SQL)  and let the
> remote (or local) machine do much of the work. For example, imagine a
> database with terabytes of data and all you want is a few rows/columns that
> meet your query. In R, you might have to open a collection of huge CSV files
> and fill more memory than you have and do the query somehow. If the data is
> remote, we are talking about a huge receiving of data. Using SQL divides the
> work so you do parts here and parts there.
>
> Why use a local MYSQL? Part of the answer is that you have a fairly
> optimized and debugged system that does it well and lets the programmer
> focus on the parts they need to add within R like complex analyses. Part is
> portability, as you can later move the data outside your machine and with
> minor changes, your program should still work. And, there are many other
> scenarios such as wanting to gather data from different sources such as
> connecting to multiple remote databases and getting filtered data and doing
> an analysis across that data and perhaps updating them.
>
> R used in ways like this provides lots of flexibility. But part of the
> question is like asking why there are a hundred programming languages still
> in use out there. Why do we need so many? In short, we don't necessarily
> need all or even most of them but they are there because various people
> developed them and used them and it is not trivial to get people to switch
> and maybe abandon all the older software or try to rewrite it.
>
> Having said that, I think a large fraction of R users have never had any
> particular reason to learn SQL. Many have never used it directly or even
> indirectly. I know someone who I have programmed for who calls some expert
> to do a SQL query and save the results in CSV files and then works directly
> in R on those files. I have pointed out to them that their life could be
> even easier if they got a more focused dump of the SQL data with some of the
> added processing done in SQL and then a smaller amount of data coming into
> the R side.
>
> I also note that languages like R and python can have parts that run fairly
> slowly. Arguably, most versions of SQL have been tuned over decades ...
>
>
> -Original Message-
> From: R-help  On Behalf Of akshay kulkarni
> Sent: Wednesday, December 11, 2024 8:17 AM
> To: R help Mailing list 
> Subject: [R] SQL and R
>
> dear Members,
>  I have recently started studying SQL and MySQL.
> My question is, what exactly is SQL used for? That is, whatever can be done
> by SQL, like subsetting and filtering of data sets, can also be done by R.
> What's, then, the advantage of SQL?  It is OK if you tag this question as
> offtopic, but I could'nt find any info on the web. Can you please refer me

SQL
> complement R? Are b

Re: [R] SQL and R

[akshay kulkarni]

Dear Bert,
   THanks a lot

From: Bert Gunter 
Sent: Wednesday, December 11, 2024 9:25 PM
To: akshay kulkarni 
Cc: R help Mailing list 
Subject: Re: [R] SQL and R

Just a slight technical note -- Ben gave you a good answer already, imo.

The note is: R is Turing complete, which mean that *anything* any
language can do, R could be programmed to do also. The point is what
can be done well in R and what can be done (often much) better with
other tools, as Ben explained.

Cheers,
Bert

On Wed, Dec 11, 2024 at 5:17 AM akshay kulkarni  wrote:
>
> dear Members,
> I have recently started studying SQL and MySQL. 
> My question is, what exactly is SQL used for? That is, whatever can be done 
> by SQL, like subsetting and filtering of data sets, can also be done by R. 
> What's, then, the advantage of SQL?  It is OK if you tag this question as 
> offtopic, but I could'nt find any info on the web. Can you please refer me to 
> some online resources that shed some light on this? Finally, how does SQL 
> complement R? Are both dependent?
>
> THanking you,
> Yours sincerely,
> AKSHAY M KULKARNI
>
> [https://s-install.avcdn.net/ipm/preview/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif]<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
>   
> Virus-free.www.avast.com<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
>
> [[alternative HTML version deleted]]
>
> __
> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

[[alternative HTML version deleted]]

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

Re: [R] SQL and R

[Jeff Newmiller via R-help]

Some people prefer SQL syntax. Also, SQL implementations are generally 
intrinsically linked with persistent disk storage, so it works 
straightforwardly with data sets larger than RAM. Finally, most implementations 
support shared access to the data from multiple clients.

A long time ago in a computer with little RAM I used to use SQL a lot. But I 
have not really used it for many years now. Your needs may vary.

On December 11, 2024 5:16:59 AM PST, akshay kulkarni  
wrote:
>dear Members,
>I have recently started studying SQL and MySQL. My 
> question is, what exactly is SQL used for? That is, whatever can be done by 
> SQL, like subsetting and filtering of data sets, can also be done by R. 
> What's, then, the advantage of SQL?  It is OK if you tag this question as 
> offtopic, but I could'nt find any info on the web. Can you please refer me to 
> some online resources that shed some light on this? Finally, how does SQL 
> complement R? Are both dependent?
>
>THanking you,
>Yours sincerely,
>AKSHAY M KULKARNI
>
>[https://s-install.avcdn.net/ipm/preview/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif]
>  
>Virus-free.www.avast.com
>
>   [[alternative HTML version deleted]]
>
>__
>R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.

-- 
Sent from my phone. Please excuse my brevity.

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

Re: [R] SQL and R

[CALUM POLWART]

And to answer the dependency question.

Neither is dependent on the other. But both can be complimentary.

If you consider that SQL*may* be a route to accessing your data (if it's in
a database).

And R *may* be a route to analysis of the data.

If the data is in a CSV file, Excel file, API etc.  you don't need SQL. IF
it is in a database, you might extract it to CSV etc. or you might directly
access it from R

If ALL you want is some simple number counts you can do that in SQL. NO
NEED FOR R. So for instance you could have a database for a warehouse.
There could be three tables in that database (you'll have far more).

Orders

Customers

Products

Products lists all your products - with a product code.

Customers lists all the customers with a customer code

And orders lists the customer code, date and product ordered.

SQL will let you create a "view" of that data that shows the customer
address, and product name.

SQL can also tell you how many of a product were ordered by postal area in
a month even though the data is not organised like that.

R can do that analysis too. But it could do far more statistical analysis
and say run a stats test to see if male customers are more likely to buy
beer than female.  I don't think that's possible in SQL.

Sometimes you could just pull the whole database into R and analyse it. But
if you imagine that database was actually every sale made in Walmart in the
last 10 years, the database is huge.  It would be better to extract the
beer sales and males and females only which is hopefully smaller to
analyse...

On Wed, 11 Dec 2024, 21:39 Sorkin, John,  wrote:

> Dear Askay,
>
> I believe my grey hair allows me to help answer your question. SQL, and
> its progenitor SEQUEL, were developed specifically to manipulate relational
> databases. It was developed in the early 1970s (equivalent to the
> historical bronze age) when the concept of a relational database (see
> https://en.wikipedia.org/wiki/Relational_database) and Codd's 12-rules
> were being developed (see https://en.wikipedia.org/wiki/Codd%27s_12_rules)
>
> At the time, the concept of a relation database and a programming language
> dedicated to manipulating them was revolutionary. The concept was clearly
> needed, important, and well used; a commercial version of SQL, Oracle, made
> Larry Ellison more than a quarter billionaire.
>
> S, one of the progenitors of R, was developed later. In 1975 by John
> Chambers, Rick Becker, Trevor Hastie, and William Cleveland (all of whom, I
> believe worked at Bell Labs) developed S as a general programming language.
> It was NOT developed specifically for the manipulation of relational
> databases. S had modest success in academia. S-Plus, a commercial version
> of R was developed fairly recently in 1988 by a company Statistical
> Sciences. The founder of Statistical Sciences was R. Douglas Marin who was
> a professor of statistics at the University of Washington, Seattle.
>
> S was also the progenitor of R. R was developed by Ross Ihaka and Robert
> Gentlemen in 1993, faculty members of the University of Auckland. Given the
> ubiquity of R in academia, it is clear that S, much like SQL has been
> extraordinarily successful.
>
> John
>
>
>
> John David Sorkin M.D., Ph.D.
> Professor of Medicine, University of Maryland School of Medicine;
> Associate Director for Biostatistics and Informatics, Baltimore VA Medical
> Center Geriatrics Research, Education, and Clinical Center;
> PI Biostatistics and Informatics Core, University of Maryland School of
> Medicine Claude D. Pepper Older Americans Independence Center;
> Senior Statistician University of Maryland Center for Vascular Research;
>
> Division of Gerontology and Paliative Care,
> 10 North Greene Street
> 
> GRECC (BT/18/GR)
> Baltimore, MD 21201-1524
> Cell phone 443-418-5382
>
>
>
>
> 
> From: R-help  on behalf of akshay kulkarni <
> akshay...@hotmail.com>
> Sent: Wednesday, December 11, 2024 8:16 AM
> To: R help Mailing  list
> Subject: [R] SQL and R
>
> dear Members,
> I have recently started studying SQL and
> MySQL. My question is, what exactly is SQL used for? That is, whatever can
> be done by SQL, like subsetting and filtering of data sets, can also be
> done by R. What's, then, the advantage of SQL?  It is OK if you tag this
> question as offtopic, but I could'nt find any info on the web. Can you
> please refer me to some online resources that shed some light on this?
> Finally, how does SQL complement R? Are both dependent?
>
> THanking you,
> Yours sincerely,
> AKSHAY M KULKARNI
>
> [
> https://s-install.avcdn.net/ipm/preview/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif
> ]<
> https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
> Virus-free.http://www.avast.com/<
> https://www.avast.com/sig-em

Re: [R] SQL and R

[Sorkin, John]

Dear Askay,

I believe my grey hair allows me to help answer your question. SQL, and its 
progenitor SEQUEL, were developed specifically to manipulate relational 
databases. It was developed in the early 1970s (equivalent to the historical 
bronze age) when the concept of a relational database (see 
https://en.wikipedia.org/wiki/Relational_database) and Codd's 12-rules were 
being developed (see https://en.wikipedia.org/wiki/Codd%27s_12_rules)

At the time, the concept of a relation database and a programming language 
dedicated to manipulating them was revolutionary. The concept was clearly 
needed, important, and well used; a commercial version of SQL, Oracle, made 
Larry Ellison more than a quarter billionaire.

S, one of the progenitors of R, was developed later. In 1975 by John Chambers, 
Rick Becker, Trevor Hastie, and William Cleveland (all of whom, I believe 
worked at Bell Labs) developed S as a general programming language. It was NOT 
developed specifically for the manipulation of relational databases. S had 
modest success in academia. S-Plus, a commercial version of R was developed 
fairly recently in 1988 by a company Statistical Sciences. The founder of 
Statistical Sciences was R. Douglas Marin who was a professor of statistics at 
the University of Washington, Seattle.

S was also the progenitor of R. R was developed by Ross Ihaka and Robert 
Gentlemen in 1993, faculty members of the University of Auckland. Given the 
ubiquity of R in academia, it is clear that S, much like SQL has been 
extraordinarily successful.

John



John David Sorkin M.D., Ph.D.
Professor of Medicine, University of Maryland School of Medicine;
Associate Director for Biostatistics and Informatics, Baltimore VA Medical 
Center Geriatrics Research, Education, and Clinical Center;
PI Biostatistics and Informatics Core, University of Maryland School of 
Medicine Claude D. Pepper Older Americans Independence Center;
Senior Statistician University of Maryland Center for Vascular Research;

Division of Gerontology and Paliative Care,
10 North Greene Street
GRECC (BT/18/GR)
Baltimore, MD 21201-1524
Cell phone 443-418-5382





From: R-help  on behalf of akshay kulkarni 

Sent: Wednesday, December 11, 2024 8:16 AM
To: R help Mailing  list
Subject: [R] SQL and R

dear Members,
I have recently started studying SQL and MySQL. My 
question is, what exactly is SQL used for? That is, whatever can be done by 
SQL, like subsetting and filtering of data sets, can also be done by R. What's, 
then, the advantage of SQL?  It is OK if you tag this question as offtopic, but 
I could'nt find any info on the web. Can you please refer me to some online 
resources that shed some light on this? Finally, how does SQL complement R? Are 
both dependent?

THanking you,
Yours sincerely,
AKSHAY M KULKARNI

[https://s-install.avcdn.net/ipm/preview/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif]
  
Virus-free.http://www.avast.com/

[[alternative HTML version deleted]]

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.r-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

Re: [R] SQL and R

[Eberhard W Lisse]

Looks like an assignment question.

If so, do your homework yourself. Google is your friend

el


On 2024-12-11 15:16, akshay kulkarni wrote:
> dear Members, I have recently started studying SQL and MySQL. My
> question is, what exactly is SQL used for? That is, whatever can be
> done by SQL, like subsetting and filtering of data sets, can also be
> done by R. What's, then, the advantage of SQL?  It is OK if you tag
> this question as offtopic, but I could'nt find any info on the web.
> Can you please refer me to some online resources that shed some
> light on this? Finally, how does SQL complement R? Are both
> dependent?
> 
> THanking you, Yours sincerely, AKSHAY M KULKARNI

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

Re: [R] SQL and R - tangential

[avi.e.gross]

Actually, tangentially, JC, I have a deep suspicion that many computer
languages are not written to solve problems. They are sometimes an effort by
someone to implement a new paradigm different than what others have tried
before or to protect the programmer from themselves or to demand extensive
rigor so the result almost proves itself and so on.

Only after the language is in place, might they look for a problem that
might be solved using it.

Kidding aside, some aspects of R may not have been as useful early on in
that the use of multi-dimensional arrays is not as common a need in many
areas of endeavor. But it has taken on more meaning in areas like AI. Other
languages that did not support some concepts like that well, have often had
to add it in.

The problem today in looking at computer languages is how ALIKE they are
becoming as features keep being grafted on to make each language have
features already found in another.

If you look at the question about SQL and R, you might note that although
base R had lots of functionality, some aspects you see in extensions like
dplyr are attempts to in some way mimic SQL or do things different or even
better. Consider the many clauses allowed in a SQL "select" statement and
map that onto a dplyr query in a pipeline with what seems like lots of
clauses. Base R before the pipe did not lend itself so easily to that.


-Original Message-
From: R-help  On Behalf Of J C Nash
Sent: Wednesday, December 11, 2024 11:58 AM
To: r-help@r-project.org
Subject: Re: [R] SQL and R - tangential

My late friend Morven Gentleman, not long after he stepped down from being
chair
of Computer Science at Waterloo, said that it seemed computer scientists had
to create
a new computer language for every new problem they encountered.

If we could use least squares to measure this approximation, we'd likely be
suspicious
of a terribly small error measure or overly high R^2.

JN

On 2024-12-11 11:11, avi.e.gr...@gmail.com wrote:
> Akshay,
> 
> Your question has way too many answers.
> 
> SQL has a long history and early versions came long before R arrived on
the
> scene. There is a huge embedded base of hardware and software dedicated to
> managing databases. It has some features that most R programs do not even
> dream of doing. Besides easily handling massive amounts of data or
sometimes
> tweaking queries to possibly run more efficiently, there are all kinds of
> issue of how to manage multiple people accessing and changing the data at
> about the same time, or rolling the data back to an earlier checkpoint.
> 
> R came along later and, as Ben pointed out, adds all kinds of things SQL
> does not have and likely does not need, or alternate ways to do things.
> 
> For many people now, the workload is to use a programming language, and R
is
> not the only one used, which has enhanced with packages or modules that
> allow access in a fairly general way to one or many databases running
> various versions of SQL. The programmer uses this API in many ways.
> 
> In some ways, it is just a way to tell the database what to do without
much
> other processing. You can ask to open a connection to the server, do a
query
> that gets translated to SQL (or you can provide the actual SQL)  and let
the
> remote (or local) machine do much of the work. For example, imagine a
> database with terabytes of data and all you want is a few rows/columns
that
> meet your query. In R, you might have to open a collection of huge CSV
files
> and fill more memory than you have and do the query somehow. If the data
is
> remote, we are talking about a huge receiving of data. Using SQL divides
the
> work so you do parts here and parts there.
> 
> Why use a local MYSQL? Part of the answer is that you have a fairly
> optimized and debugged system that does it well and lets the programmer
> focus on the parts they need to add within R like complex analyses. Part
is
> portability, as you can later move the data outside your machine and with
> minor changes, your program should still work. And, there are many other
> scenarios such as wanting to gather data from different sources such as
> connecting to multiple remote databases and getting filtered data and
doing
> an analysis across that data and perhaps updating them.
> 
> R used in ways like this provides lots of flexibility. But part of the
> question is like asking why there are a hundred programming languages
still
> in use out there. Why do we need so many? In short, we don't necessarily
> need all or even most of them but they are there because various people
> developed them and used them and it is not trivial to get people to switch
> and maybe abandon all the older software or try to rewrite it.
> 
> Having said that, I think a large fraction of R users have never had any
> particular reason 

Re: [R] SQL and R - tangential

[J C Nash]

My late friend Morven Gentleman, not long after he stepped down from being chair
of Computer Science at Waterloo, said that it seemed computer scientists had to 
create
a new computer language for every new problem they encountered.

If we could use least squares to measure this approximation, we'd likely be 
suspicious
of a terribly small error measure or overly high R^2.

JN

On 2024-12-11 11:11, avi.e.gr...@gmail.com wrote:

Akshay,

Your question has way too many answers.

SQL has a long history and early versions came long before R arrived on the
scene. There is a huge embedded base of hardware and software dedicated to
managing databases. It has some features that most R programs do not even
dream of doing. Besides easily handling massive amounts of data or sometimes
tweaking queries to possibly run more efficiently, there are all kinds of
issue of how to manage multiple people accessing and changing the data at
about the same time, or rolling the data back to an earlier checkpoint.

R came along later and, as Ben pointed out, adds all kinds of things SQL
does not have and likely does not need, or alternate ways to do things.

For many people now, the workload is to use a programming language, and R is
not the only one used, which has enhanced with packages or modules that
allow access in a fairly general way to one or many databases running
various versions of SQL. The programmer uses this API in many ways.

In some ways, it is just a way to tell the database what to do without much
other processing. You can ask to open a connection to the server, do a query
that gets translated to SQL (or you can provide the actual SQL)  and let the
remote (or local) machine do much of the work. For example, imagine a
database with terabytes of data and all you want is a few rows/columns that
meet your query. In R, you might have to open a collection of huge CSV files
and fill more memory than you have and do the query somehow. If the data is
remote, we are talking about a huge receiving of data. Using SQL divides the
work so you do parts here and parts there.

Why use a local MYSQL? Part of the answer is that you have a fairly
optimized and debugged system that does it well and lets the programmer
focus on the parts they need to add within R like complex analyses. Part is
portability, as you can later move the data outside your machine and with
minor changes, your program should still work. And, there are many other
scenarios such as wanting to gather data from different sources such as
connecting to multiple remote databases and getting filtered data and doing
an analysis across that data and perhaps updating them.

R used in ways like this provides lots of flexibility. But part of the
question is like asking why there are a hundred programming languages still
in use out there. Why do we need so many? In short, we don't necessarily
need all or even most of them but they are there because various people
developed them and used them and it is not trivial to get people to switch
and maybe abandon all the older software or try to rewrite it.

Having said that, I think a large fraction of R users have never had any
particular reason to learn SQL. Many have never used it directly or even
indirectly. I know someone who I have programmed for who calls some expert
to do a SQL query and save the results in CSV files and then works directly
in R on those files. I have pointed out to them that their life could be
even easier if they got a more focused dump of the SQL data with some of the
added processing done in SQL and then a smaller amount of data coming into
the R side.

I also note that languages like R and python can have parts that run fairly
slowly. Arguably, most versions of SQL have been tuned over decades ...


-Original Message-
From: R-help  On Behalf Of akshay kulkarni
Sent: Wednesday, December 11, 2024 8:17 AM
To: R help Mailing list 
Subject: [R] SQL and R

dear Members,
 I have recently started studying SQL and MySQL.
My question is, what exactly is SQL used for? That is, whatever can be done
by SQL, like subsetting and filtering of data sets, can also be done by R.
What's, then, the advantage of SQL?  It is OK if you tag this question as
offtopic, but I could'nt find any info on the web. Can you please refer me
to some online resources that shed some light on this? Finally, how does SQL
complement R? Are both dependent?

THanking you,
Yours sincerely,
AKSHAY M KULKARNI

[https://s-install.avcdn.net/ipm/preview/icons/icon-envelope-tick-round-oran
ge-animated-no-repeat-v1.gif]
Virus-free.www.avast.com

[[alternative HTML version deleted]]

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, se

Re: [R] SQL and R

[avi.e.gross]

Akshay,

Your question has way too many answers.

SQL has a long history and early versions came long before R arrived on the
scene. There is a huge embedded base of hardware and software dedicated to
managing databases. It has some features that most R programs do not even
dream of doing. Besides easily handling massive amounts of data or sometimes
tweaking queries to possibly run more efficiently, there are all kinds of
issue of how to manage multiple people accessing and changing the data at
about the same time, or rolling the data back to an earlier checkpoint.

R came along later and, as Ben pointed out, adds all kinds of things SQL
does not have and likely does not need, or alternate ways to do things.

For many people now, the workload is to use a programming language, and R is
not the only one used, which has enhanced with packages or modules that
allow access in a fairly general way to one or many databases running
various versions of SQL. The programmer uses this API in many ways.

In some ways, it is just a way to tell the database what to do without much
other processing. You can ask to open a connection to the server, do a query
that gets translated to SQL (or you can provide the actual SQL)  and let the
remote (or local) machine do much of the work. For example, imagine a
database with terabytes of data and all you want is a few rows/columns that
meet your query. In R, you might have to open a collection of huge CSV files
and fill more memory than you have and do the query somehow. If the data is
remote, we are talking about a huge receiving of data. Using SQL divides the
work so you do parts here and parts there.

Why use a local MYSQL? Part of the answer is that you have a fairly
optimized and debugged system that does it well and lets the programmer
focus on the parts they need to add within R like complex analyses. Part is
portability, as you can later move the data outside your machine and with
minor changes, your program should still work. And, there are many other
scenarios such as wanting to gather data from different sources such as
connecting to multiple remote databases and getting filtered data and doing
an analysis across that data and perhaps updating them.

R used in ways like this provides lots of flexibility. But part of the
question is like asking why there are a hundred programming languages still
in use out there. Why do we need so many? In short, we don't necessarily
need all or even most of them but they are there because various people
developed them and used them and it is not trivial to get people to switch
and maybe abandon all the older software or try to rewrite it.

Having said that, I think a large fraction of R users have never had any
particular reason to learn SQL. Many have never used it directly or even
indirectly. I know someone who I have programmed for who calls some expert
to do a SQL query and save the results in CSV files and then works directly
in R on those files. I have pointed out to them that their life could be
even easier if they got a more focused dump of the SQL data with some of the
added processing done in SQL and then a smaller amount of data coming into
the R side.

I also note that languages like R and python can have parts that run fairly
slowly. Arguably, most versions of SQL have been tuned over decades ...


-Original Message-
From: R-help  On Behalf Of akshay kulkarni
Sent: Wednesday, December 11, 2024 8:17 AM
To: R help Mailing list 
Subject: [R] SQL and R

dear Members,
I have recently started studying SQL and MySQL.
My question is, what exactly is SQL used for? That is, whatever can be done
by SQL, like subsetting and filtering of data sets, can also be done by R.
What's, then, the advantage of SQL?  It is OK if you tag this question as
offtopic, but I could'nt find any info on the web. Can you please refer me
to some online resources that shed some light on this? Finally, how does SQL
complement R? Are both dependent?

THanking you,
Yours sincerely,
AKSHAY M KULKARNI

[https://s-install.avcdn.net/ipm/preview/icons/icon-envelope-tick-round-oran
ge-animated-no-repeat-v1.gif]
Virus-free.www.avast.com

[[alternative HTML version deleted]]

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide
https://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
and p

Re: [R] SQL and R

[Bert Gunter]

Just a slight technical note -- Ben gave you a good answer already, imo.

The note is: R is Turing complete, which mean that *anything* any
language can do, R could be programmed to do also. The point is what
can be done well in R and what can be done (often much) better with
other tools, as Ben explained.

Cheers,
Bert

On Wed, Dec 11, 2024 at 5:17 AM akshay kulkarni  wrote:
>
> dear Members,
> I have recently started studying SQL and MySQL. 
> My question is, what exactly is SQL used for? That is, whatever can be done 
> by SQL, like subsetting and filtering of data sets, can also be done by R. 
> What's, then, the advantage of SQL?  It is OK if you tag this question as 
> offtopic, but I could'nt find any info on the web. Can you please refer me to 
> some online resources that shed some light on this? Finally, how does SQL 
> complement R? Are both dependent?
>
> THanking you,
> Yours sincerely,
> AKSHAY M KULKARNI
>
> [https://s-install.avcdn.net/ipm/preview/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif]
>   
> Virus-free.www.avast.com
>
> [[alternative HTML version deleted]]
>
> __
> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

Re: [R] SQL and R

[Ben Bolker]

  Others may know more than I do, but roughly:

  (1) SQL provides access to relational database management systems 
that are much more robust and handle large-scale data;
  (2) methods based on SQL will often handle data that are too large to 
fit in memory


  R complements SQL by providing a much  larger set of statistical 
tools. The typical workflow would be that you would use SQL queries to 
do the extraction, subsetting, and simple manipulation of data to reduce 
the data to a manageable size for analysis on a single machine, then use 
R to analyze it.


  See also various packages (arrow, dbplyr) that provide alternative 
big-data workflows.


  cheers
   Ben Bolker


On 12/11/24 08:16, akshay kulkarni wrote:

dear Members,
 I have recently started studying SQL and MySQL. My 
question is, what exactly is SQL used for? That is, whatever can be done by 
SQL, like subsetting and filtering of data sets, can also be done by R. What's, 
then, the advantage of SQL?  It is OK if you tag this question as offtopic, but 
I could'nt find any info on the web. Can you please refer me to some online 
resources that shed some light on this? Finally, how does SQL complement R? Are 
both dependent?

THanking you,
Yours sincerely,
AKSHAY M KULKARNI

[https://s-install.avcdn.net/ipm/preview/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif]
  
Virus-free.www.avast.com

[[alternative HTML version deleted]]

__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.


--
Dr. Benjamin Bolker
Professor, Mathematics & Statistics and Biology, McMaster University
Director, School of Computational Science and Engineering
* E-mail is sent at my convenience; I don't expect replies outside of 
working hours.


__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.